폴더 조회 시 썸네일을 못가져오는 문제

[hyejungg]

What happened? 🤷‍♀️

폴더 조회 시 가장 최근에 등록된 아이템의 이미지를 폴더 썸네일로 사용하기 위해 쿼리를 아래와 같이 수정하였습니다.

SELECT f.folder_id, f.folder_name, i.item_img folder_thumbnail, ifnull(ic.item_count, 0) item_count
FROM folders f LEFT OUTER JOIN (SELECT folder_id, item_img FROM items ORDER BY create_at DESC LIMIT 1) i
ON f.folder_id = i.folder_id 
LEFT OUTER JOIN (SELECT folder_id, count(folder_id) item_count FROM items GROUP BY folder_id) ic
ON f.folder_id = ic.folder_id
WHERE f.user_id = ? GROUP BY f.folder_id ORDER BY f.create_at DESC;

해당 쿼리는 item_count 값은 정상적으로 가져오지만, 썸네일 부분에서 다음과 같은 문제를 초래합니다.

• 무분별한 아이템 이미지를 가져오는 문제
  • 어떤 폴더는 최근 아이템 이미지를, 어떤 폴더는 가장 오래된 아이템 이미지를 가져옴
  • 폴더 내 아이템이 없는 경우에도 아이템 이미지를 썸네일로 보여줌

How to encounter bug 🐛

폴더 조회의 경우, 해당 유저의 1)모든 폴더 정보를 가져올 때 2)아이템의 개수만큼 카운트하여 가져오고, 3)아이템이 있는 경우 가장 최근에 등록된 아이템을 가져 오도록합니다.

우선 해당 버그의 주 원인을 해결하기 위해 3)최근 아이템의 썸네일을 가져오는 코드부터 수정하였습니다.

select a.folder_id, a.item_img as folder_thumbnail, a.create_at
from items a inner join (select max(create_at) as create_at from items group by folder_id) b
where 1 = 1 and a.create_at = b.create_at and user_id = 13;

• item 테이블에 대하여 자기 자신(item 테이블)을 inner join 하여 create_at가 가장 최근인 아이템 이미지와 날짜를 출력하도록 하고, folder_id를 기준으로 출력을 희망하였기 때문에 group by로 folder_id를 지정하였습니다.
• b 내에서 folder_id 별로 가장 최신의 create_at를 가져와 item_img를 보여준다.
• inner join을 수행하였기 때문에 조건에 해당되는 행만 출력됩니다.

폴더 조회의 경우, 1)사용자가 등록한 모든 폴더를 조회하여야 하기 때문에 folders 테이블과 left outer join 하여 없는 경우는 null로 표현되도록 합니다.

select f.folder_id, f.folder_name, i.folder_thumbnail from folders f 
left outer join (
(select a.folder_id, a.item_img as folder_thumbnail, a.create_at
from items a inner join (select max(create_at) as create_at from items group by folder_id) b
where 1 = 1 and a.create_at = b.create_at and user_id = 13)) i
on f.folder_id = i.folder_id WHERE f.user_id = 13;

• 이렇게 지정하면, 아이템 정보가 없는 경우 폴더 썸네일은 null을 띄웁니다.

마지막으로, 3) 폴더 내 아이템의 개수를 보내줘야 하기 때문에 이 역시 없는 경우 0으로 count 처리 하기 위해 item 테이블과 left outer join 합니다.

select f.folder_id, f.folder_name, i.folder_thumbnail, ifnull(ic.item_count, 0) item_count from folders f 
left outer join (
(select a.folder_id, a.item_img as folder_thumbnail, a.create_at
from items a inner join (select max(create_at) as create_at from items group by folder_id) b
where 1 = 1 and a.create_at = b.create_at and user_id = 13)) i
on f.folder_id = i.folder_id 
LEFT OUTER JOIN (SELECT folder_id, count(folder_id) item_count FROM items GROUP BY folder_id) ic
ON f.folder_id = ic.folder_id
WHERE f.user_id = 13 ORDER BY f.create_at DESC;

• 이렇게 지정하면 저희가 원하는 결과를 표시할 수 있습니다.
• 가장 마지막에 WHERE f.user_id = 13를 추가하지 않으면 모든 user의 폴더 정보를 보여주기 때문에 추가하였습니다.
• ORDER BY f.create_at DESC를 지정하여 해당 내용들이 folder 테이블에 들어간 행을 기준으로 최신순으로 정렬하여 보여줍니다.

최종쿼리

select f.folder_id, f.folder_name, i.folder_thumbnail, ifnull(ic.item_count, 0) item_count from folders f 
left outer join (
(select a.folder_id, a.item_img as folder_thumbnail, a.create_at
from items a inner join (select max(create_at) as create_at from items group by folder_id) b
where a.create_at = b.create_at)) i
on f.folder_id = i.folder_id 
LEFT OUTER JOIN (SELECT folder_id, count(folder_id) item_count FROM items GROUP BY folder_id) ic
ON f.folder_id = ic.folder_id
WHERE f.user_id = 13 ORDER BY f.create_at DESC;

• where 1 = 1의 경우 참을 의미하는 내용이기 때문에 없어도 무방하다 판단되어 삭제
• inner join 한 a, b 테이블에 대하여 where 조건에 user_id = 13은 쿼리 가장 마지막에 user_id 검증을 해주므로 없어도 무방하다 판단되어 삭제

참고

블로그-SQL Group by에서 최신 row 데이터 가져오기