0

I am creating a trending feed of videos for an iPhone app and am calculating all of the views on a video in the last 24 hours in a views table (a row is added to the views table everytime it is views and has a timestamp and videoid). When I do infinite scrolling for it I send back the count of the last video views and I use the HAVING statement in mySQL to get the next videos

"HAVING COUNT(views.videoid) < 'count of views'" with a "LIMIT 10" at the end to select 10 at at time.

This works, however if a video has the same number of views as another video in the last 24 hours then that video could get skipped because it is a < statement. And if I use a <= statement videos will show up multiple times.

Is there a way that I could select videos after a videoid after the 

"ORDER BY COUNT(views.videoid) DESC"

to get the next video in line? Probably not but does anyone know how I would go about doing that. Here is my query.

SELECT f.userid, f.username, video.videoid, video.videolink, video.timestamp, video.caption, video.tags, video.address, video.likecount, video.commentcount, video.viewcount, video.shareid, CASE WHEN liketable.likekey > 0 THEN 1 ELSE 0 END AS didlike, COUNT(views.videoid) AS trendcount

FROM user AS f

RIGHT JOIN video ON video.userid = f.userid

LEFT JOIN user ON user.token = ?

LEFT JOIN liketable ON liketable.videoid = video.videoid AND liketable.userid = user.userid

RIGHT JOIN views ON views.videoid = video.videoid

WHERE views.timestamp > ?

GROUP BY video.videoid

HAVING COUNT(views.videoid) < ?

ORDER BY COUNT(views.videoid) DESC

LIMIT 10
Cœur
  • 37,241
  • 25
  • 195
  • 267
stuyam
  • 9,561
  • 2
  • 21
  • 40

1 Answers1

2

You can use offsets with LIMIT:

SELECT [...] LIMIT 20,10;

This retrieves 10 rows, starting at row 20 in the results.

Then if you set your ORDER BY to order by count and video id, you'll always get the results in the same order and shouldn't skip any.

Kadaan
  • 1,154
  • 1
  • 8
  • 13