dimanche 12 juin 2016

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

Aucun commentaire:

Enregistrer un commentaire