How to limit the number of rows through LIMIT?

Question

MySQL

Can't limit output of avatars to 5 and lastnames 2 already reviewed a bunch of



Advanced SQL articles and courses

Array
(
    [0] => Array
        (
            [user_id] => 1
            [avatar] => author-page.jpg
            [firstname] => James
            [lastname] => Spiegel
            [created_at] => 2021-08-18 10:42:17
            [post_images] => {"1": "post-photo1.jpg", "2": "post-photo1.jpg"}
            [id] => 1
            [title] => My Perfect Vacations in South America and Europe
            [description] => Lorem ipsum dolor sit amet, consectadipisicing elit, sed do eiusmod por incidid ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud lorem exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis en aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.


Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium doloremque laudantium, totam rem aperiam, eaque ipsa quae ab illo inventore veritatis et quasi architecto beatae vitae dicta sunt explicabo. Nemo enim ipsam voluptatem quia voluptas sit aspernatur aut odit aut fugit, sed quia consequuntur magni dolores eos qui ratione voluptatem sequi nesciunt  
            [count_likes] => 6
            [avatars] => {"1": "author-page.jpg", "2": "friend-harmonic5.jpg", "3": "friend-harmonic4.jpg", "4": "friend-harmonic3.jpg", "5": "friend-harmonic2.jpg", "6": "friend-harmonic1.jpg"}
            [lastnames] => {"1": "Spiegel", "2": "Lname2", "3": "Lname3", "4": "Lname4", "5": "Lname5", "6": "Lname6"}
            [count_comments] => 0
            [count_shares] => 0
            [count_image] => 2
        )

    [1] => Array
        (
            [user_id] => 1
            [avatar] => author-page.jpg
            [firstname] => James
            [lastname] => Spiegel
            [created_at] => 2021-08-18 10:42:17
            [post_images] => {"4": "post__thumb4.jpg"}
            [id] => 2
            [title] => Advices for Backpacking
            [description] => Lorem ipsum dolor sit amet, consectadipisicing elit, sed do eiusmod por incidid ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud lorem exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. 

Duis en aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium doloremque laudantium, totam rem aperiam, eaque ipsa quae ab illo inventore veritatis et quasi architecto beatae vitae dicta sunt explicabo. Nemo enim ipsam voluptatem quia voluptas sit aspernatur aut odit aut fugit, sed quia consequuntur magni dolores eos qui ratione voluptatem sequi nesciun Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium doloremque laudantium, totam rem
            [count_likes] => 2
            [avatars] => {"1": "author-page.jpg", "3": "friend-harmonic4.jpg"}
            [lastnames] => {"1": "Spiegel", "3": "Lname3"}
            [count_comments] => 0
            [count_shares] => 0
            [count_image] => 1
        )

)

SELECT (SELECT users.id FROM users WHERE users.id = posts.user_id) as user_id,
                    (SELECT users.avatar FROM users WHERE users.id = posts.user_id) as avatar,
                    (SELECT users.firstname FROM users WHERE users.id = posts.user_id) as firstname,
                    (SELECT users.lastname FROM users WHERE users.id = posts.user_id) as lastname,
                    posts.created_at,
                    JSON_OBJECTAGG(images.id, images.img) AS post_images,
                    posts.id,
                    posts.title,
                    posts.description,
                    (
                        SELECT COUNT(likes.id)
                        FROM likes
                        WHERE likes.post_id = posts.id
                    ) as count_likes,
                    JSON_OBJECTAGG(users.id, users.avatar) AS avatars,
                    JSON_OBJECTAGG(users.id, users.lastname) AS lastnames,
                    (
                        SELECT COUNT(comments.id)
                        FROM comments
                        WHERE comments.post_id = posts.id
                    ) as count_comments,
                    (
                        SELECT COUNT(shares.id)
                        FROM shares
                        WHERE shares.post_id = posts.id
                    ) as count_shares,
                    (
                        SELECT COUNT(images.id)
                        FROM images
                        WHERE images.post_id = posts.id
                    ) as count_image
                FROM posts
                    INNER JOIN images ON images.post_id = posts.id
                    INNER JOIN likes ON likes.post_id = posts.id
                    INNER JOIN users ON users.id = likes.user_id AND posts.user_id = 1
                GROUP BY posts.id

Answer 1

Simplified query example using row_number

select 
  posts.*,
  json_objectagg(likers.user_id, likers.lname) lastnames,
  json_objectagg(likers.user_id, likers.avatar) avatars
from posts
join (
  select post_id, user_id, fname, lname, avatar, row_number() over (partition by post_id) rn
  from likes
  join users on users.id = likes.user_id
) likers on likers.post_id = posts.id
where likers.rn < 6
group by posts.id, posts.title, posts.description
;

Share SQL queries online