How to fetch the top comments?

Question

I've searched everywhere else, but I could not find any reference or tutorials that shows you or explain a best way to show top comments (like the one on facebook, or youtube).

I have accomplished fetching records (20 top comments that is order by their votes). But where I always get stuck at is how am I gonna supposed to fetch the next 20 top comments

Below is what my tables looks like:

comments table

id  |  comments       | comment_id
1      Hi               nj3b21das
2      Cool             jh3lkjb32
3      How are you?     bn32j1343
4      What's up?       3kl213543

votes_comments table (1 is equal to thumbs up, and -1 is equal to thumbs down)

id  |  user_id       | comment_id   |  votes
1      4326542         nj3b21das        1
2      2356453         jh3lkjb32        -1
3      8764354         bn32j1343        1
4      3213543         3kl213543        1

Then I combined these two tables to get the top comments by using the query below:

    SELECT `comments`.comments, SUM(`votes_comments`.votes) AS total_votes
    FROM comments
    LEFT JOIN `votes_comments `                 
    ON `votes_comments`.comment_id = `comments`.comment_id
    GROUP BY comment_id
    ORDER BY total_votes
    DESC
    LIMIT 20

The query above will fetch the first 20 top comments. But what if I want to fetch the next 20 results using ajax and leave the previous records displayed, what is the best option for this?

Note: Keep in mind that the votes are changing constantly. Therefore, fetching the next top 20 comments will might result a duplication of the comments that has been fetched already. What is the best way to handle this (Like Facebook, Youtube etc..).

Hi - regarding your last question about those crazy unicode characters, this question may be of interest to you: http://stackoverflow.com/questions/10414864/whats-up-with-these-unicode-characters — Adam Rackis, Dec 16 '13 at 07:06

score 1 · Accepted Answer · answered Dec 05 '13 at 03:42

There are 2 ways i can think of.

One is, you can store the current shown comment ids in an array and pass those with your ajax call and then filter in SQL statement exclude those comment ids and fetch the rest of the top comments and once you get the response, append those ids to the same array and you can continue with this.

Or

You define a timestamp on page load that indicates the server time (Ex: var loadTimeStamp = "<?php echo time(); ?>") and then pass this value along with limit as part of your Ajax call.

Then on the server side, you can exclude any comments that were added after this time, hence preserve the comments list. But for this to work, you need to store the time when people comment and looking at the AJAX calls facebook makes for fetching more comments, they seem to be following this type of method as i can see they pass timestamp with the call (I might be wrong, but their AJAX calls pass timestamp, so I'm assuming..)

With this method, you can go one step further and use this time along with a basic ajax long polling technique to notify the user of any new comments since the page was loaded/last loading of new comments, similar to Facebook and Twitter feeds.

Hope you got it.

Ivan Gabriele · Answer 2 · 2016-06-11T21:23:18.007

0

You just have to precise from which response you want to begin in the LIMIT By the way I added votes_comments. inside GROUP BY to avoid ambiguous error in MySQL.

SELECT `comments`.comments, SUM(`votes_comments`.votes) AS total_votes
FROM comments
LEFT JOIN `votes_comments`                 
ON `votes_comments`.comment_id = `comments`.comment_id
GROUP BY `votes_comments`.comment_id
ORDER BY total_votes
DESC
LIMIT 20, 20

edited Jun 11 '16 at 21:23

answered Dec 05 '13 at 02:40

Ivan Gabriele

6,433
5
39
60

Like I said " I want to fetch the next 20 results using ajax and leave the previous records displayed." Doing something like LIMIT 20, 20, will might result a duplication of a comment that has been fetched already since votes are changing continuously. – ILikebanana Dec 05 '13 at 02:43
But you just have to precise the parameter in the AJAX Url you're calling like `?begin=20&number=20` and you keep tracking the `begin` variable by storing it in a hidden input (or through the `window.my_variable` as explained here : http://www.ivangabriele.com/how-to-set-a-global-variable-in-javascript/). You increment this variable while you detect the scrolling, you have many jquery & co plugins for that. – Ivan Gabriele Dec 05 '13 at 02:52
Here are some plugins that allow that : http://jscroll.com/ or http://imakewebthings.com/jquery-waypoints/ (very easy-to-use). – Ivan Gabriele Dec 05 '13 at 02:55
So what if I do this "?begin=20&number=20"? LIKE I SAID " LIMIT 20, 20, will might result a duplication of a comment that has been fetched already since votes are changing continuously" Want me to repeat it again? " LIMIT 20, 20, will might result a duplication of a comment that has been fetched already since votes are changing continuously". I do not have a problem handling the parameter I pass in Ajax url or the function of scrol. Just in case you already forget " LIMIT 20, 20, will might result a duplication of a comment that has been fetched already since votes are changing continuously" – ILikebanana Dec 05 '13 at 03:01
Then you only have two choices : or you refresh everything from the result 0 until the actual `number` (which depends on the scroll position), or you have to create a script that will store `comment_id` and `votes_number` in an array, check if some comments have their `votes_number` changed (through an ajax call with a cache system stored in $_SESSION var : `comment_id-votes_number|comment_id-votes_number|...`), and move the comments through jquery. You just have to give an id for each div that matches the `comment_id`. Then compare the ids+votes returned by the ajax with the JS array. – Ivan Gabriele Dec 05 '13 at 03:37

How to fetch the top comments?

2 Answers2