Group_concat внутри другого group_concat?

Question

[Kneepy]

Есть следующий запрос

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

Но вывод answers происходит по одному к каждому комментарию, а не группой. То есть когда я пытаюсь вывести ответы для одного комментария, выводятся они не под одним комментарием, а по одному под каждым. Как нужно перестроить запрос чтобы это работало как было упомянуто выше?

Comments

[Akina]

Убрать его в подзапрос либо СТЕ и делать второе агрегирование во внешнем запросе. Иных вариантов нет - двойная группировка недопустима.

Ещё вариант - использовать на одном из уровней оконную версию агрегатной функции, если поддерживается.

[Kneepy]

Akina, если не трудно, могли бы вы показать как это сделать в моём случае?

[Akina]

Покажите пример вывода своего запроса (3-4 строки) и то, что нужно получить вместо них. Само собой сократите длинные строки.

[Kneepy]

Akina,

title:  String,
  supplement: String,
  link: String,
  file:  String,
  avatar: String, 
  pid: String, 
  lastname: String,
  name: String
  uid: String
  comments: '[
       {
           "name": "Fama ",
           "avatar": "",
           "lastname": "fama", 
           "value": "g",
           "cid": "1630984831010", 
           "answers": [{
                 "name":  String,
                  "avatar": String, 
                  "lastname": String 
                  "value": String, 
                   "cid": String 
                    "answer": String
       }]

[Rsa97]

Ответы на ответы могут быть, или только один уровень?

[Kneepy]

Rsa97, могут

[Rsa97]

Kneepy, Тогда одним запросом древовидный JSON не получить. Максимум - получить всё дерево построчно и строить JSON уже в приложении.

[Kneepy]

Rsa97, то-есть нельзя рекурсией добавлять данные в JSON_ARRAY? Ну тогда предположим что ответы на ответы не могут существовать, тогда как это сделать?

[Immortal_pony]

Ну тогда предположим что ответы на ответы не могут существовать, тогда как это сделать?

LEFT JOIN и IF(COUNT(answers)=0, NULL, JSON_ARRAY(...))

Answer 1

[Kneepy]

Кому интересна судьба этого запроса, то я пришёл к такому решению

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

[Rsa97]

Если максимум - это ответы на комментарии, то как-то так:

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

Если нужно дерево комментариев, то получать всё дерево построчно и строить JSON в приложении.

Comments

[Kneepy]

К сожалению, это решение выводит только один комментарий и ответы к нему

[Rsa97]

Kneepy, Возможно ошибка в JOIN. Я не понял назначение поля `comments`.`answer`.