TIL | mysql / concat / GROUP_CONCAT / JSON_OBJECT / ORDER BY / GROUP BY

SELECT p.unique_id  AS post_id
	, u.id AS id
	, u.unique_id AS user_id
	, co.unique_id AS comment_id
	, co.content 
	, co.create_at AS comment_create_at
	, co.update_at AS comment_update_at
	, us.score 
	, concat(
	'[',	-- concat()으로 하기 때문에 대괄호 감싸줘야한다
	 GROUP_CONCAT(	-- ORDER BY() 사용하려고 JSON_ARRAYAGG() -> GROUP_CONCAT()
	        JSON_OBJECT(
	         'post_id', p.unique_id, 
			'id' , u2.id,
			'user_id', u2.unique_id,
			'user_score', us2.score ,
			'comment_id' , co2.unique_id,
			'content', co2.content, 
			'comment_create_at'	, co2.create_at,
			'comment_update_at'	, co2.update_at
	        )ORDER BY co2.create_at),
        ']'
	   ) as comment_in_comment	
  FROM (SELECT * FROM comments com WHERE com.LEVEL  = 1) AS co
  JOIN user_scores us ON us.user_id = co.user_id
  JOIN posts p ON p.unique_id = co.post_id 
  JOIN users u ON u.unique_id = co.user_id
  --여기 아래부터 주석 표시한 곳까지 JSON_OBJECT()에 맞춰서 JOIN
  LEFT JOIN (SELECT * FROM comments com WHERE com.LEVEL  = 2) AS co2 ON co2.parent_id = co.unique_id AND co2.post_id =1
  LEFT JOIN user_scores us2 ON us2.user_id = co2.user_id
  LEFT JOIN users u2 ON u2.unique_id = co2.user_id
  --
 WHERE p.unique_id = 1
   AND co.post_id =1
 GROUP BY co.unique_id, us.score
 ORDER BY co.create_at

• 댓글테이블을 하나로 하고 level 컬럼으로 댓글 / 댓글에 댓글을 구분해주고 하나의 댓글에 대댓글들을 로우 하나로 댓글과 대댓글 데이터를 다 가지고 가도록 만들었다