Home > Backend Development > ๐Ÿ“š[Backend Development] ๋ฌดํ•œ depth ๋Œ“๊ธ€ ์ •๋ ฌ ๊ตฌ์กฐ๋ž€ ๋ฌด์—‡์ผ๊นŒ์š”?

๐Ÿ“š[Backend Development] ๋ฌดํ•œ depth ๋Œ“๊ธ€ ์ •๋ ฌ ๊ตฌ์กฐ๋ž€ ๋ฌด์—‡์ผ๊นŒ์š”?
Backend Ddevelopment

โ€œ๐Ÿ“š[Backend Development] ๋ฌดํ•œ depth ๋Œ“๊ธ€ ์ •๋ ฌ ๊ตฌ์กฐ๋ž€ ๋ฌด์—‡์ผ๊นŒ์š”?โ€

โœ… ๋ฌดํ•œ Depth ๋Œ“๊ธ€ ์ •๋ ฌ ๊ตฌ์กฐ

  • ๋ฌดํ•œ depth ๋Œ“๊ธ€์„ ํŽ˜์ด์ง• ์ฒ˜๋ฆฌํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” ํŠธ๋ฆฌ ๊ตฌ์กฐ๋ฅผ ์œ ์ง€ํ•˜๋ฉด์„œ ์ •๋ ฌํ•˜๋Š” ์ „๋žต์ด ํ•„์š”ํ•ฉ๋‹ˆ๋‹ค.
  • ๋ณดํ†ต parent_comment_id + comment_id ์ •๋ ฌ ๋ฐฉ์‹์„ ์‚ฌ์šฉํ•˜๋Š” 2-depth ๋Œ“๊ธ€๊ณผ๋Š” ๋‹ฌ๋ฆฌ, ๋ฌดํ•œ depth์˜ ๊ฒฝ์šฐ ๋Œ“๊ธ€์˜ ๊ณ„์ธต ๊ตฌ์กฐ๋ฅผ ์œ ์ง€ํ•  ์ˆ˜ ์žˆ๋„๋ก ์ •๋ ฌ ๋ฐฉ์‹์ด ๊ฐœ์„ ๋˜์–ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

๐Ÿš€1๏ธโƒฃ ํŠธ๋ฆฌ ๊ตฌ์กฐ ๊ธฐ๋ฐ˜ ์ •๋ ฌ ๋ฐฉ๋ฒ•

  • ๋ฌดํ•œ depth์˜ ๋Œ“๊ธ€์„ ์ •๋ ฌํ•˜๋ ค๋ฉด ๋‹ค์Œ๊ณผ ๊ฐ™์€ ๋ฐฉ์‹์ด ๊ฐ€๋Šฅํ•ฉ๋‹ˆ๋‹ค.

1๏ธโƒฃ ์ •๋ ฌ ๋ฐฉ์‹

  • root_comment_id (์ตœ์ƒ์œ„ ๋ถ€๋ชจ ID) ์˜ค๋ฆ„์ฐจ์ˆœ
  • path (ํŠธ๋ฆฌ ์ˆœ์„œ) ์˜ค๋ฆ„์ฐจ์ˆœ
  • comment_id (์ž‘์„ฑ ์ˆœ์„œ) ์˜ค๋ฆ„์ฐจ์ˆœ
    • ์ด๋ ‡๊ฒŒ ์ •๋ ฌํ•˜๋ฉด ํŠธ๋ฆฌ ๊ตฌ์กฐ๋ฅผ ์œ ์ง€ํ•˜๋ฉด์„œ ๋Œ“๊ธ€์„ ์‹œ๊ฐ„์ˆœ์œผ๋กœ ์ •๋ ฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

๐Ÿš€2๏ธโƒฃ ํŠธ๋ฆฌ ๊ตฌ์กฐ๋ฅผ ์œ ์ง€ํ•˜๋Š” ์ •๋ ฌ ํ•„๋“œ

ํ•„๋“œ๋ช… ์„ค๋ช…
comment_id ๋Œ“๊ธ€์˜ ๊ณ ์œ  ID (๊ธฐ๋ณธ ํ‚ค)
parent_comment_id ๋ถ€๋ชจ ๋Œ“๊ธ€์˜ ID (์ตœ์ƒ์œ„ ๋Œ“๊ธ€์ด๋ฉด NULL)
root_comment_id ์ตœ์ƒ์œ„ ๋ถ€๋ชจ ๋Œ“๊ธ€์˜ ID (์ตœ์ƒ์œ„ ๋Œ“๊ธ€์ด๋ฉด ์ž๊ธฐ ์ž์‹  comment_id)
depth ๋Œ“๊ธ€์˜ ๊นŠ์ด (0๋ถ€ํ„ฐ ์‹œ์ž‘)
path ํŠธ๋ฆฌ ๊ตฌ์กฐ๋ฅผ ๋‚˜ํƒ€๋‚ด๋Š” ์ •๋ ฌ์šฉ ๋ฌธ์ž์—ด

๐Ÿš€3๏ธโƒฃ ์ •๋ ฌ ์ˆœ์„œ SQL ์˜ˆ์ œ

๐Ÿ“Œ ๋ฌดํ•œ Depth ์ •๋ ฌ์„ ์œ„ํ•œ ORDER BY

SELECT * FROM comment
WHERE article_id = ?
ORDER BY root_comment_id ASC, path ASC, comment_id ASC
LIMIT ?, ?;

๐Ÿ“Œ ์ •๋ ฌ ๊ธฐ์ค€.

  • 1. root_comment_id ASC โžž ์ตœ์ƒ์œ„ ๋ถ€๋ชจ ๋Œ“๊ธ€ ๊ธฐ์ค€์œผ๋กœ ์ •๋ ฌ
  • 2. path ASC โžž ํŠธ๋ฆฌ ๊ตฌ์กฐ๋ฅผ ์œ ์ง€ํ•˜๋ฉด์„œ ์ •๋ ฌ
  • 3. comment_id ASC โžž ๊ฐ™์€ depth ๋‚ด์—์„œ ์ž‘์„ฑ ์ˆœ์„œ๋Œ€๋กœ ์ •๋ ฌ

๐Ÿš€4๏ธโƒฃ path ํ•„๋“œ๋ž€?

  • ํŠธ๋ฆฌ ๊ตฌ์กฐ๋ฅผ ํ‘œํ˜„ํ•˜๊ธฐ ์œ„ํ•ด path ํ•„๋“œ๋ฅผ ํ™œ์šฉํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
    • path๋Š” ๋ถ€๋ชจ-์ž์‹ ๊ด€๊ณ„๋ฅผ ๋ช…ํ™•ํ•˜๊ฒŒ ํ•˜์—ฌ ์ •๋ ฌ์„ ์šฉ์ดํ•˜๊ฒŒ ํ•ฉ๋‹ˆ๋‹ค.
    • ์˜ˆ๋ฅผ ๋“ค์–ด, path๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์€ ๋ฐฉ์‹์œผ๋กœ ์ €์žฅ๋  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
comment_id parent_comment_id root_comment_id depth path
1 NULL 1 0 00001
2 1 1 1 00001.00002
3 1 1 1 00001.00003
4 2 1 2 00001.00002.00004
5 4 1 3 00001.00002.00004.00005
  • ๐Ÿ“Œ ์ •๋ ฌ ์‹œ ORDER BY path ASC๋ฅผ ์‚ฌ์šฉํ•˜๋ฉด ๊ณ„์ธต ๊ตฌ์กฐ๋ฅผ ์œ ์ง€ํ•˜๋ฉด์„œ ์ •๋ ฌ ๊ฐ€๋Šฅ!

๐Ÿš€5๏ธโƒฃ ์ •๋ฆฌ

  • โœ… ๋ฌดํ•œ depth ๋Œ“๊ธ€ ์ •๋ ฌ์„ ์œ„ํ—ค path ๋˜๋Š” lft/rgt ๋ฐฉ์‹์ด ํ•„์š”
  • โœ… ์ •๋ ฌ ์ˆœ์„œ๋Š” root_comment_id ASC, path ASC, comment_id ASC ๋ฐฉ์‹ ์‚ฌ์šฉ
  • โœ… ํŽ˜์ด์ง• ์ฒ˜๋ฆฌ ์‹œ LIMIT ?,? ์ ์šฉ ๊ฐ€๋Šฅ
    • ๐Ÿ“Œ ๊ธฐ์กด 2-depth ๋ฐฉ์‹์ฒ˜๋Ÿผ parent_comment_id ์ •๋ ฌ๋งŒ์œผ๋กœ๋Š” ๋ฌดํ•œ depth ์ •๋ ฌ์ด ์–ด๋ ค์šฐ๋ฏ€๋กœ path๋ฅผ ํ™œ์šฉํ•˜๋Š” ๊ฒƒ์ด ๊ฐ€์žฅ ์ ์ ˆํ•ฉ๋‹ˆ๋‹ค.