MySQL limit and not so limited

My basic table design is comment id, comment, previous comment id
Top level comments have Null for the previous comment id

This gives an easy nested reply system. The problem is I need to do paging on the comments in some way that I can limit the total number of comments on the page to 25 BUT if the nested comments exceed 25 then show them anyway but still limit the number of comments to a reasonable number

This is more help with the logic, because the more I think about it the more my brain becomes addled
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Please post the CREATE TABLE statement and the INSERT statements that will load up your test table.  This is often called the SSCCE, and it will let us see exactly what you're looking at.  Once we have the test data we can start experimenting with potential solutions, and hopefully come up with a good one!
NicholasAuthor Commented:
Table design is in my original question
This is a question for SQL guys, only added PHP as it's vaguely related for the backend
You provided a description of the structure Ray's request deals with the complete definition including index, types..
Your issue and difficulty is that you are assembling data meaning the 25 limit deals with unique references versus a total of ...

Post the query you have

Presumably you would like to avoid orphan records so if I've query/comment has 100 prior comments what is the cutoff ?
What if you have 24 lines and the next comment is a sequence of 10,25 how many do you want displayed or display only the 24.

I think I understand what you want, but I do not believe you can achieve this through the SQL query, nor post processing on php because of the business logic/conditions.
One option could be to identify the 25 unique comments and avail the user the option to click on the one of interest to them the result will be the display of the entire nested comment chain.

I.e similar to thread assembly.
Thread1 (10)
Thread2 (20)
Thread3 (30)

This will simplify both your business process as well as provide your users robust and straightforward way to access data of interest to them instead of having to page through pages of possibly unimportant information wasting both the user's time and the time to figure out every possible variation .....

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

NicholasAuthor Commented:
One option could be to identify the 25 unique comments and avail the user the option to click on the one of interest to them the result will be the display of the entire nested comment chain.

Hmm that could be a definite possibility and get the data thru ajax
The nested replies are usually no more than about 10...

The question is a broad question about the logic of such a request - I don't think there is an easy solution to this

Using your suggestion then I really only need to check the number of Null value comments for paging - the more I think about it the more I like it and have a clicky to read the nested responses without having some massive page at load time

Will ponder this a while to see if there is any negatives to it else I think that is the perfect answer
Ray PaseurCommented:
PHP Topic area removed "as it's vaguely related."  I think you will probably get better solutions with a more focused approach.
NicholasAuthor Commented:
Why remove PHP when there could be a PHP solution, there could very well be a PHP solution in the backend for it?
Just because I was more focused on the SQL side does not mean there are other viable solutions like datatables now I think of it.
How frequently is this query run data needed.

To use PHP creating/handling large amounts of data.

How deep do your comments
Do nested comments consist of parent/child or grandparents and grandchildren, additional generations are involved?
Comment1is a child of commenta and a parent of commentI  as well as a grandparent of coments_1 which is the child of commentI

My suggestion is a PHP option where the data is included ... The consideration whether ......
NicholasAuthor Commented:
The query is run constantly, comments normally are no more than 100 - which probably makes datatables viable
The child comments can go, usually no more than, 10 levels deep (as in a reply to a reply to a reply etc) - there is the odd exception
It seems as though your setup is similar to various bulletin board, wiki type of setup that you want to create on your own.
So you have a single table as you outlined with a comment that is a reply to the prior comment
Do you have a dummy data example four deep?
You have select * from table a left join table b on a.previous_comment_id=b.commentid
sql servers are not very good with recursivity and it is a pain to implement. fetching "arbitrary" depth usually boils to running a query with a number of left joins and expect to get NULLs towards the end...

if you expect to pull all comments at once, and you don't have multiple answers to the same comment, you probably should modify the "nested logic" and use a thread id and the dates of the posts.

if not, pulling for example the first N levels ( 2 or 3 i guess ) seems reasonable, and you can develop only the first level by default on screen. and you can use ajax to pull additional levels as the user opens branches

is your model a series of threads or a tree ?
any feedback ?
NicholasAuthor Commented:
No feedback, still contemplating how best to approach/solve this

I think as I have it now is probably the easiest solution
NicholasAuthor Commented:
Haven't worked out everything about it yet, but seems the best solution
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.