-
카테고리
-
세부 분야
백엔드
-
해결 여부
미해결
다대다 데이터 조회 질문입니다.
23.03.23 14:08 작성 23.03.23 14:54 수정 조회수 279
0
강사님 강의를 보고 최근에 프로젝트를 하다 궁금한게 생겨 질문 남기게 되었습니다.
Product <-> Category Entity 다대다 매핑을 위해
product(OneToMany) <-> product_category(ManyToOne) <-> category(OneToMany)
위와 같은 테이블로 매핑한 상태이고 join 상속 전략으로 district_category 테이블도 생성 하였습니다.
district_category를 쿼리 파라미터로 받고 해당 district_category를 갖고 있는 상품들을 검색하고 페이징을 하는 로직을 짠다고 했을때 두가지 방법이 생각났는데 둘 중 어느게 더 적합한지 모르겠습니다
검색어를 바탕으로 product_category에서 Product를 EntityGraph로 같이 찾은 후에 찾은 ProductCategory.getproduct() 와 같은 방식
SearchService
Category category = districtCategoryRepository.findByDistrictEnum(districtEnum)
.orElseThrow(() -> new ProductException(ProductExceptionType.CATEGORY_NOT_FOUND));
return new ProductCategoryToProductPage(productCategoryRepository.findAllByCategory(pageable, category));
ProductCategoryRepository
@EntityGraph(attributePaths = "product")
Page<ProductCategory> findAllByCategory(Pageable pageable, Category category);
ProductCategoryToProduct
public ProductCategoryToProductPage(Page<ProductCategory> page) {
this.content.addAll(page.getContent().stream()
.map(ProductCategory::getProduct)
.map(ProductListGetResponseDTO::new)
.collect(toList()));
this.totalPages = page.getTotalPages();
this.totalElements = page.getTotalElements();
this.pageNumber = page.getNumber() + 1;
this.size = page.getSize();
}
발생 쿼리문
select
districtca0_.category_id as category2_1_,
districtca0_1_.parent_id as parent_i3_1_,
districtca0_.district_enum as district1_2_
from
district_category districtca0_
inner join
category districtca0_1_
on districtca0_.category_id=districtca0_1_.category_id
where
districtca0_.district_enum=?
select
productcat0_.product_category_id as product_1_8_0_,
product1_.product_id as product_1_7_1_,
productcat0_.category_id as category2_8_0_,
productcat0_.product_id as product_3_8_0_,
product1_.created_date as created_2_7_1_,
product1_.content_detail as content_3_7_1_,
product1_.product_content as product_4_7_1_,
product1_.product_name as product_5_7_1_,
product1_.product_price as product_6_7_1_,
product1_.product_status as product_7_7_1_,
product1_.product_thumbnail as product_8_7_1_
from
product_category productcat0_
left outer join
product product1_
on productcat0_.product_id=product1_.product_id
where
productcat0_.category_id=? limit ?
select
count(productcat0_.product_category_id) as col_0_0_
from
product_category productcat0_
where
productcat0_.category_id=?
검색어를 바탕으로 product에서 직접 찾기
(데이터 뻥튀기의 문제는 쿼리dsl 이용 productId로 groupBy로 해결)글 쓰고 생각해보니 A카테고리는 B라는 상품 안에서는 하나밖에 있을 수가 없으니 굳이 groupBy를 안써도 될거 같네요
SearchService
Category category = districtCategoryRepository.findByDistrictEnum(districtEnum)
.orElseThrow(() -> new ProductException(ProductExceptionType.CATEGORY_NOT_FOUND));
return productRepository.findAllByCategory(pageable, category);
ProductRepository
@Override
public Page<Product> findAllByCategory(Pageable pageable, Category category) {
List<Product> content = queryFactory.selectFrom(product)
.join(product.productCategories, productCategory)
.where(productCategory.category.categoryId.eq(category.getCategoryId()))
.groupBy(product.productId)
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetch();
Long total = queryFactory
.select(Wildcard.count)
.from(product)
.join(product.productCategories, productCategory)
.where(productCategory.category.categoryId.eq(category.getCategoryId()))
.fetchOne();
return new PageImpl<>(content, pageable, total);
}
발생 쿼리문
select
districtca0_.category_id as category2_1_,
districtca0_1_.parent_id as parent_i3_1_,
districtca0_.district_enum as district1_2_
from
district_category districtca0_
inner join
category districtca0_1_
on districtca0_.category_id=districtca0_1_.category_id
where
districtca0_.district_enum=?
select
product0_.product_id as product_1_7_,
product0_.created_date as created_2_7_,
product0_.content_detail as content_3_7_,
product0_.product_content as product_4_7_,
product0_.product_name as product_5_7_,
product0_.product_price as product_6_7_,
product0_.product_status as product_7_7_,
product0_.product_thumbnail as product_8_7_
from
product product0_
inner join
product_category productcat1_
on product0_.product_id=productcat1_.product_id
where
productcat1_.category_id=?
group by
product0_.product_id limit ?
select
count(*) as col_0_0_
from
product product0_
inner join
product_category productcat1_
on product0_.product_id=productcat1_.product_id
where
productcat1_.category_id=?
답변을 작성해보세요.
1
김영한
지식공유자2023.03.23
안녕하세요. 주우민님
이런 경우는 딱 정답이 있는 것은 아니고 원하는 요구사항에 맞추어 문제를 해결하면 됩니다.
기술적으로는 SQL 쿼리를 최소화 할 수 있고, DB에서 데이터를 최대한 적게 조회해서 해결하는 방안을 선택하시는 것이 대부분 성능상 더 나은 선택입니다.
감사합니다.
답변 1