MySQL LEFT JOIN LIMIT for each row

Question

I would like to get a list of Posts including the last 2 comments for each particular post (if any). I have been using the query below, that returns all the comments for each post, however it has a limitation, because the GROUP_CONCAT() length is limited to 1024 characters by default, so if there are a lot of comments on a post, the 'comments' value will be cut off, and it won't be a valid JSON output anymore.

SELECT post.*,
    CONCAT('[', GROUP_CONCAT(DISTINCT JSON_OBJECT(
        'id', postComment.id,
        'date', postComment.date,
        'content', postComment.content,
        'user', CONCAT(postComment.fName, postComment.lName)
    ) ORDER BY postComment.id DESC),']') AS comments
FROM posts AS post
    LEFT JOIN (
        SELECT comment.*, commentUser.fName, commentUser.lName
        FROM comments AS comment
            LEFT JOIN users AS commentUser ON comment.uID = commentUser.id
        ORDER BY comment.id DESC
    ) AS postComment ON postComment.postID = post.id
WHERE post.uID = 37
GROUP BY post.id
ORDER BY post.id DESC
LIMIT 0,5;

Due to this limitation, I was thinking of returning only the last 2 comments for each post by adding LIMIT 0,2 to the LEFT JOIN SELECT clause like this:

SELECT post.*,
    CONCAT('[', GROUP_CONCAT(DISTINCT JSON_OBJECT(
        'id', postComment.id,
        'date', postComment.date,
        'content', postComment.content,
        'user', CONCAT(postComment.fName, postComment.lName)
    ) ORDER BY postComment.id DESC),']') AS comments
FROM posts AS post
    LEFT JOIN (
        SELECT comment.*, commentUser.fName, commentUser.lName
        FROM comments AS comment
            LEFT JOIN users AS commentUser ON comment.uID = commentUser.id
        ORDER BY comment.id DESC
        LIMIT 0, 2
    ) AS postComment ON postComment.postID = post.id
WHERE post.uID = 37
GROUP BY post.id
ORDER BY post.id DESC
LIMIT 0,5;

But now it returns the first two comment for the very first post only...

Could anyone tell me how can I get this work correctly, so the query results return the first two comments FOR EACH particular post and why this is happening?

Cheers!

It's unusual to include a [SELECT] DISTINCT modifier in a query that contains aggregation. For further help, please see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Oct 22 '20 at 15:43

score 1 · Answer 1 · answered Oct 22 '20 at 16:26

As a starter: if your actual problem is the limitation on the number of characters returned by GROUP_CONCAT(), you can increase it by modifying session variable group_concat_max_len, like so:

SET SESSION group_concat_max_len = 1000000;

Then you can run your query (in the same session).

As for your question itself: I would recommend turning the LEFT JOIN to a correlated subquery; basically the subquery is re-executed for each and every post, so you can use ORDER BY and LIMIT:

SELECT p.*, 
    CONCAT(
        '[', 
        (
            SELECT GROUP_CONCAT(
                JSON_OBJECT(
                    'id', c.id,
                    'date', c.date,
                    'content', c.content,
                    'user', CONCAT(u.fName, u.lName)
                )
                ORDER BY c.id DESC
            )
            FROM comments AS c
            LEFT JOIN users AS u ON c.uID = u.id
            WHERE c.postID = p.id
            ORDER BY c.date DESC
            LIMIT 2
        ), 
        ']'
    ) AS comments
    FROM posts AS p
WHERE p.uID = 37
GROUP BY p.id
ORDER BY p.id DESC
LIMIT 0,5;

As a final thought: do upgrade to MySQL 5.7.22 or higher! Then you can use JSON_ARRAYAGG(), and you won't have to worry about this all anymore.

Thank you, this solution taught me to avoid the `GROUP_CONCAT()` clause and use the `JSON_ARRAYAGG()` instead, however I've still trying to get my head around the comment limitation problem, but for some reason the `LIMIT` clause is ignored in your code above. — Csaba, Oct 22 '20 at 19:50

MySQL LEFT JOIN LIMIT for each row

1 Answers1