갯수 카운트와 join을 같이 써서 나타난 문제 해결하기

발생한 문제

나의 카테고리 조회 로직은 다음과 같다

List<CategoryWithHistoryResponseDto> response = jpaQueryFactory.select(Projections.constructor(
				CategoryWithHistoryResponseDto.class,
				category.categoryId,
				category.memberId,
				category.thumbnail,
				category.title,
				category.isDeleted,
				category.isShared,
				category.createdAt,
				category.modifiedAt,
				cardHistory.score.score.avg(),
				cardHistory.cardId.countDistinct(),
				card.cardId.countDistinct()
			))
			.from(category)
			.leftJoin(card)
			.on(category.categoryId.eq(card.categoryId))
			.leftJoin(cardHistory)
			.on(card.cardId.eq(cardHistory.cardId))
			.where(
				category.isDeleted.eq(false),
				eqMemberId(memberId),
				dynamicCursorExpression(lastCategoryId))
			.groupBy(category.categoryId)
			.orderBy(category.categoryId.uuid.desc())
			.limit(pageSize + 1)
			.fetch();

그러나 해당 로직은 cardId.countDistinct()가 조회에 문제가 있었다. 도메인을 설계할때 삭제 상태를 나타내는 isDeleted 속성을 뒀는데 카드를 삭제하면 isDeleted = true가 되고 실제로 데이터를 삭제하지는 않는다. 그러나 위의 쿼리대로라면 card의 isDeleted가 true가 되더라도 count시 cardId를 카운트한다. 그 이유는 leftJoin시 cardId가 null이 아니기 때문에 isDeleted와 상관없이 카운트하기 때문이다.

where절에 추가했지만 또 다른 문제가 발생

테스트 코드를 짜고
where 절에 단순히 card의 isDeleted=false로 필터해버리면 된다고 생각했다.

그러나 결과는 예상대로 나오지 않았다.

기존에 작성해둔 테스트가 모두 통과하지 않았기 때문이다.

그 이유는 where절에 추가한 card.isDeleted.eq(false)에 있었다.

이를 where절에서 필터하면 카테고리 안에 카드가 없다면 leftJoin시 card정보는 모두 null이 된다. 그러나 where절에서 모두 필터해버리기 때문에 category 또한 조회되지 않는 문제점이 발생한다. 이를 해결하려면 다른 방법을 사용해야 한다.

on절에서 조건문 사용하기

전체 where절에서 filter하는 방법대신 on 절에서 filter해서 join하는 방법이 있다. 바로 on절에 and를 활용해 join시 조건문에 해당하는 부문한 조인할 수 있도록 하는 것이다.

List<CategoryWithHistoryResponseDto> response = jpaQueryFactory.select(Projections.constructor(
				CategoryWithHistoryResponseDto.class,
				category.categoryId,
				category.memberId,
				category.thumbnail,
				category.title,
				category.isDeleted,
				category.isShared,
				category.createdAt,
				category.modifiedAt,
				cardHistory.score.score.avg(),
				cardHistory.cardId.countDistinct(),
				card.cardId.countDistinct()
			))
			.from(category)
			.leftJoin(card)
			.on(
				category.categoryId.eq(card.categoryId),
				card.isDeleted.eq(false))
			.leftJoin(cardHistory)
			.on(card.cardId.eq(cardHistory.cardId))
			.where(
				category.isDeleted.eq(false),
				eqMemberId(memberId),
				dynamicCursorExpression(lastCategoryId))
			.groupBy(category.categoryId)
			.orderBy(category.categoryId.uuid.desc())
			.limit(pageSize + 1)
			.fetch();

실제 예상되는 sql은 다음과 같을 것이다.

SELECT c.category_id,
       c.member_id,
       c.thumbnail,
       c.title,
       c.is_deleted,
       c.is_shared,
       c.created_at,
       c.modified_at,
       AVG(ch.score),
       COUNT(DISTINCT ch.card_id),
       COUNT(DISTINCT ca.card_id)
FROM category c
LEFT JOIN card ca ON c.category_id = ca.category_id AND ca.is_deleted = false
LEFT JOIN card_history ch ON ca.card_id = ch.card_id
WHERE c.is_deleted = false
  AND c.member_id = ?
  AND c.category_id.uuid < ?
GROUP BY c.category_id
ORDER BY c.category_id.uuid DESC
LIMIT ?

그 결과 모든 테스트를 통과했다.

결론

join시에 추가 조건이 있다면 전체 쿼리에서 where절을 쓰는것보다 on절에서 join시 조건을 주는 것이 훨씬 안전하다.