Group_concat inside another group_concat?

Question

MySQL

I have the following request

SELECT
           p.title, p.supplement, p.link, p.file, uPost.avatar, p.pid, uPost.lastname, 
           CONCAT(IFNULL(uPost.firstname, ''), ' ', IFNULL(uPost.surname, '')) AS name,
           uPost.uid, 
           GROUP_CONCAT(JSON_ARRAY(
             JSON_OBJECT(
               'name', CONCAT(IFNULL(uComm.firstname, ''), " ", IFNULL(uComm.surname, '')),
               'avatar', uComm.avatar,
               'lastname', uComm.lastname,
               'value', c.value,
               'cid', c.cid, 
               'answers', JSON_ARRAY(
                  JSON_OBJECT(
                    'name', CONCAT(IFNULL(usersAnswerComm.firstname, ''), " ", IFNULL(usersAnswerComm.surname, '')), 
                    'avatar', usersAnswerComm.avatar, 
                    'lastname', usersAnswerComm.lastname, 
                    'value', cAnswer.value, 
                    'cid', cAnswer.cid, 
                    'answer', cAnswer.answer
                  )
               )
             )
           )) AS comments 
           FROM posts p 
             JOIN users uPost ON p.uid = uPost.uid AND p.pid = ?
             LEFT JOIN comments c ON c.pid = ?
             LEFT JOIN users uComm ON uComm.uid = c.uid
             LEFT JOIN comments cAnswer ON cAnswer.answer = c.cid
             JOIN users usersAnswerComm ON usersAnswerComm.uid = cAnswer.uid

But the output of answers occurs one by one for each comment, and not by a group. That is, when I try to display responses for one comment, they are displayed not under one comment, but one under each. How should the query be rebuilt to make it work as mentioned above?

Answer 1

Who cares about the fate of this request, then I came to this decision

SELECT
             p.title, p.supplement, p.link, p.file, uPost.avatar, p.pid, uPost.lastname,
             CONCAT(IFNULL(uPost.firstname, ''), ' ', IFNULL(uPost.surname, '')) AS name,
             uPost.uid, 
             GROUP_CONCAT(distinct JSON_ARRAY(
                 JSON_OBJECT(
                   'name', CONCAT(IFNULL(uComm.firstname, ''), " ", IFNULL(uComm.surname, '')),
                   'avatar', uComm.avatar,
                   'lastname', uComm.lastname,
                   'value', c.value,
                   'cid', c.cid, 
                   'isAnswer', IFNULL(c.answer, false),
                   'answers', (SELECT GROUP_CONCAT(JSON_ARRAY(JSON_OBJECT(
                   		'name', CONCAT(IFNULL(uComm.firstname, ''), " ", IFNULL(uComm.surname, '')),
                       	'avatar', uComm.avatar,
                       	'lastname', uComm.lastname,
                       	'value', commAnswer.value,
                       	'cid', commAnswer.cid
                   ))) AS answer FROM comments commAnswer WHERE commAnswer.answer = c.cid)
                 )
             )) AS comments
           FROM posts p 
             JOIN users uPost ON p.uid = uPost.uid AND p.pid = ?
             LEFT JOIN comments c ON c.pid = ?
             LEFT JOIN users uComm ON uComm.uid = c.uid

Answer 2

If the maximum is replies to comments, then something like this:

SELECT `p`.`title`, `p`.`supplement`, `p`.`link`, `p`.`file`,
      `u`.`avatar`, `p`.`pid`, `u`.`lastname`, `u`.`uid`, `c`.`comments`,
       CONCAT(IFNULL(`u`.`firstname`, ''), ' ', IFNULL(`u`.`surname`, '')) AS `name`
  FROM (
    SELECT `c`.`pid`,
           JSON_ARRAYAGG(JSON_OBJECT(
             'name', CONCAT(IFNULL(`u`.`firstname`, ''), " ", IFNULL(`u`.`surname`, '')),
             'avatar', `u`.`avatar`,
             'lastname', `u`.`lastname`,
             'value', `c`.`value`,
             'cid', `c`.`cid`,
             'answers', `a`.`answers`
           )) AS `comments`
    FROM (
        SELECT `a`.`cid`,
               JSON_ARRAYARG(JSON_OBJECT(
                 'name', CONCAT(IFNULL(`u`.`firstname`, ''), " ", IFNULL(`u`.`surname`, '')), 
                 'avatar', `u`.`avatar`, 
                 'lastname', `u`.`lastname`, 
                 'value', `a`.`value`, 
                 'cid', `a`.`cid`, 
                 'answer', `a`.`answer`
               )) AS `answers`
          FROM `comments` AS `c`
          JOIN `comments` AS `a` ON `a`.`cid` = `c`.`cid`
          LEFT JOIN `users` AS `u` ON `u`.`uid` = `a`.`uid`
          WHERE `c`.`pid` = :postId
          GROUP BY `a`.`cid`
      ) AS `a`
      RIGHT JOIN `comments` AS `c` ON `c`.`cid` = `a`.`cid`
      LEFT JOIN `users` AS `u` ON `c`.`uid` = `u`.`uid`
      WHERE `c`.`pid` = :postId
      GROUP BY `c`.`pid`
  ) AS `c`
  RIGHT JOIN `posts` AS `p` ON `p`.`pid` = `c`.`pid`
  LEFT JOIN `users` AS `u` ON `u`.`uid` = `p`.`uid`
  WHERE `p`.`pid` = :postId

If you need a comment tree, then get the entire tree line by line and build JSON in the application.