0

I need help applying a limit to a Left Join in a Select query.

I have a table with posts and another one with videos. I want to select a list of posts with some filters applied, and their respective videos, limited to 25 videos per post. Now I get ALL videos for a post, which could be hundreds and which is slow!

My current Sql looks like this:

SELECT `post`.`id` AS `post_id`,
       GROUP_CONCAT(CONCAT(`video`.`id`, ',', `video`.`timestamp`) SEPARATOR ';') AS `videos`,
       COUNT(`video`.`id`) AS `video_count`
  FROM `post`
       LEFT JOIN `video` -- Don't know how to limit these videos to 25 items
       ON `video`.`category_id` = `post`.`category_id`
       AND `video`.`file_size` > 0
       AND `video`.`timestamp_beginning`
           BETWEEN `post`.`timestamp` - INTERVAL 5 MINUTE
           AND `post`.`timestamp` + INTERVAL 5 MINUTE
 WHERE `post`.`author_id` = 2 AND `post`.`status` = 'active'
 GROUP BY `post`.`id`
 ORDER BY `post`.`id` DESC
 LIMIT 100,100;

Thank you!

Shadow
  • 33,525
  • 10
  • 51
  • 64
Max Cruer
  • 100
  • 1
  • 8
  • @SimonMartinelli No that doesn't answer my question. – Max Cruer Mar 05 '21 at 10:50
  • This is the classic get top N records per group problem. Pls see answers to the duplicate question for various solutions. Mysql v8 solution using window functions is quite at the bottom of the list of answers. – Shadow Mar 05 '21 at 11:01
  • Which field can you use to sort the films so that the LIMIT always delivers the same ones. A LIMIT without ORDER makes no sense because the sequence is not guaranteed -- and see answer from shadow – Bernd Buffen Mar 05 '21 at 11:01

0 Answers0