We help IT Professionals succeed at work.

MySQL limit and not so limited

Nicholas
Nicholas asked
on
176 Views
Last Modified: 2017-04-17
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
Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014

Commented:
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!

Author

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
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

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
Most Valuable Expert 2011
Author of the Year 2014

Commented:
PHP Topic area removed "as it's vaguely related."  I think you will probably get better solutions with a more focused approach.

Author

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.
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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 ......

Author

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
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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
...
CERTIFIED EXPERT

Commented:
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 ?
CERTIFIED EXPERT

Commented:
any feedback ?

Author

Commented:
No feedback, still contemplating how best to approach/solve this

I think as I have it now is probably the easiest solution

Author

Commented:
Haven't worked out everything about it yet, but seems the best solution

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions