CODEWITHSUNDEEP
phpmysqlsqlajaxfacebook
ILikebanana
ILikebanana

Reputation: 109

How to fetch the top comments?

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..).

Upvotes: 0

Views: 204

Answers (2)

Ivan Gabriele
Ivan Gabriele

Reputation: 6900

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

Upvotes: 0

Syed I.R.
Syed I.R.

Reputation: 6230

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.

Upvotes: 1

Related Questions