Как ограничить количество строк через LIMIT?

Question

[Speakermen]

Не получается ограничить вывод avatars до 5 и lastnames 2 уже пересмотрел кучу статей и курсов



Advanced SQL

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

Comments

[Василий Банников]

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

А не проще написать

SELECT u.id, u.furstname, u.lastname, u.avatar FROM users u WHERE u.id =...

?

[Speakermen]

Василий Банников, я бы с радостью #1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'app_draft.users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Чуть позже опубликую тут https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0cb822...

Answer 1

[Slava Rozhnev]

Упрощенный пример запроса использующий 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

Comments

[Speakermen]

Спасибо огромное!