I have a website in which I’m loading all of the users’ comments by the number of likes they have. This has caused me some troubles.
Let’s say comment #1 has 5 likes, comment #2 has 2 likes, and the rest have 0 likes. We have the following comment id’s:
1 2 3 4 5 6 7 8 9 10
Now let’s assume someone else “likes” comment #4. Now the id’s ordered are:
1 2 4 3 5 6 7 8 9 10
Let’s assume I’m loading 3 comments at the same time. At scenario #1, I loaded comments:
{1, 2, 3}
Now when I click “load more,” I load:
{3, 5, 6}
Loaded #3 twice! And comment #4 is never viewed. Problems also happen when a comment is deleted, but that’s for later.
How can I work around that? I saw websites that order by “likes” or something similar and I almost never encounter duplicates and/or missing comments. Any tips?
Thanks.
9
It sounds like you’re using naive “LIMIT 3” and then “LIMIT 3,3” to return only those additional records required. Doing so will, quite obviously, result in duplicates and missed comments if a “like” is recorded intra-session.
To resolve that issue, you may want to try one of the following approaches:
- Rather than the
OFFSETclause, send aJSONarray of all comment ID’s already shown to the user, and exclude them from the viable options via aWHERE IN(1,2,3)clause. - As above, but store the array of comment ID’s shown server-side, in the user’s current session or even a MySQL temporary table.
- Don’t send the entire “new comments” in a single request. Instead, have the page request an index of comment ID’s and their sortable attributes, and then ask for “comment text” or “comment changes” via distinct requests.
- Don’t omit any comment from the list sent to the client. Instead, include ALL comments in each request, and descide which ones to show client-side.
Be aware that none of the above are without potential performance issues, but one of the approaches will be required if you want to sort by any mutable property, “Likes” or what have you.
3