LIMIT MySQL subquery using variable value before LEFT JOIN

Question

I have this MySQL table posts:

id | content       | parentid | userid
--------------------------------------
01 | Post test #1  | 0        | 1
02 | Post test #2  | 0        | 1
03 | Comment #1    | 1        | 2
04 | Comment #2    | 1        | 1
05 | Post test #3  | 0        | 3
06 | Comment #3    | 1        | 2
07 | Comment #4    | 2        | 5
08 | Comment #5    | 5        | 6
09 | Comment #6    | 1        | 4
10 | Post test #4  | 0        | 4

This is just an example for stackoverflow

Now I need to LIMIT comments for each post and so far I have used this query:

SELECT
    `posts`.`id` AS `post_id`,
    `posts`.`content` AS `post_content`,
    `posts`.`parentid` AS `post_parentid`,
    `posts`.`userid` AS `post_userid,

    `comments`.`id`, 0 AS `comment_id`,
    `comments`.`content` AS `comment_content`,
    `comments`.`parentid` AS `comment_parentid`,
    `comments`.`userid` AS `comment_userid,
    IF( IFNULL( `comments`.`id`, 0 ) > 0, "comment", "post" ) AS `contenttype`

FROM `posts` AS `posts`
LEFT JOIN ( SELECT "" AS `hello` ) AS `useless` ON @pid := `posts`.`id`
LEFT JOIN ( SELECT
        `posts`.`id` AS `id`,
        `posts`.`id` AS `id`,
        `posts`.`id` AS `id`,
        `posts`.`id` AS `id`
    FROM `posts`
    WHERE `posts`.`parentid` = @pid
    LIMIT 10
    ) AS `comments`ON `comments`.`parentid` = `posts`.`id`
WHERE
    `posts`.`userid` = {USERID}

To archive this I have joined an useless "table" just to update @pid (parentid) variable.

Is this the only way to LIMIT subquery results? I don't like the idea of that useless JOIN.

What if I have to LIMIT posts in the example above without affecting the comments LIMIT. Can you please give me a better query?

Answer 1

The real reason of posting this question was to load 10 comments with 10 sub-comments for each comment. On the question I have asked to load posts & comments so the idea is the same.

The example posted in my question doesn't works because the subquery will executed before the variable @pid gets updated.

Because I'm using PHP then I'm posting here the solution in MySQL & PHP for this situation.

1 - First let's load posts with this SQL query

SELECT
    `posts`.`id` AS `id`,
    `posts`.`content` AS `content`,
    `posts`.`parentid` AS `parentid`,
    `posts`.`userid` AS `userid

 FROM `posts` AS `posts`
 WHERE
    `posts`.`userid` = {USERID}
    AND
    `posts`.`parentid` = '0'
 ORDER BY `posts`.`id` DESC
 LIMIT 10

2 - Store posts information in $posts array:

$posts   = [];
while   ( $row = $result->fetch_object() )
        {
        $posts[] = (object) [ "id"       => $row->id,
                              "content"  => $row->content,
                              "userid"   => $row->userid,
                              "comments" => []
                              ];
        }

3 - Prepare SQL to load comments:

$size    = count( $posts );
$sql     = "";
for     ( $i = 0; $i < $size; $i ++ )
        {
        $sql     .= ( $sql != "" ? "UNION ALL " : "" )
                 . "( "
                 . "SELECT "
                 . "`comments`.`id` AS `id`, "
                 . "`comments`.`content` AS `content`, "
                 . "`comments`.`parentid` AS `parentid`, "
                 . "`comments`.`userid` AS `userid "
                 . "FROM `posts` AS `comments` "
                 . "WHERE "
                 . "`comments`.`parentid` = '" . $post[ $i ]->id . "' "
                 . "ORDER BY `comments`.`id` ASC "
                 . "LIMIT 10 "
                 . ") ";
        }

4 - After executing the $sql code let's store comments for each post:

while ( $row = $result->fetch_object() )
      {
      $posts[ $row->parentid ]->comments[] = (object)[
                               "id"       => $row->id,
                               "content"  => $row->content,
                               "userid"   => $row->userid,
                               ];
      }

As you can see this can be used for also comments (instead of posts ) & sub-comments (instead of comments). MySQL variables are not helpful this time. Of course to create a pagination you have to add additional field (replies) in the table and update that during comment creation.

If someone has a better solution is welcomed.