Link to home
Avatar of Nicholas

asked on

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
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

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!
Avatar of Nicholas


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
Avatar of arnold
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
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
PHP Topic area removed "as it's vaguely related."  I think you will probably get better solutions with a more focused approach.
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 ......
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 ?
No feedback, still contemplating how best to approach/solve this

I think as I have it now is probably the easiest solution
Haven't worked out everything about it yet, but seems the best solution