[BE-기술] 채식 유형 계층형 검색 쿼리 만들기

안녕하세요 NOT-ERROR-064팀의 백엔드 개발자 황윤준입니다. 

이번 프로젝트의 핵심 기능 중 하나인 '선택한 채식 유형에 맞춘 상품 조회'를 구현하기 위한 쿼리를 구현한 방법에 대해 블로깅하겠습니다. 

해당 쿼리를 구현하기 위한 요구사항을 알아보겠습니다.

저희는 채식주의자의 스펙트럼을 고려하여 자신의 스펙트럼에 맞는 상품만 노출되는 형태로 쇼핑몰을 제작하기로 하였습니다. 저희는 제품 등록 시 제품의 채식유형을 한가지만 가질 수 있도록 했습니다. 또한 회원도 하나의 유형만 가질 수 있습니다.  그런데 문제는....

바로 채식 유형 스펙트럼에 있었습니다. 위 표에서 볼 수 있듯이 채식 유형별로 먹을 수 있는 식재료(제품)이 계층형으로 포함되는 경우가 있습니다.

예를 들어, 락토를 선택한 회원이 상품을 조회할 땐 비건과 프루테리언이 섭취 가능한 제품도 먹을 수 있으므로 비건, 프루테리언, 락토 유형의 제품이 함.께. 등장해야 한다는 것이죠. 여기서 오보 제품이 나오면 안됩니다!

 

게다가 이 재료의 섭취 가능 여부가 조금씩 차이가 있어 유형을 트리 형태로 두어 상하위 계층으로 단순하게 나눌 수 있는 구조가 아니었습니다. 일반적인 트리형 구조로 표현이 가능하다면 이전 카테고리 무한뎁스 형태 구현처럼 parent와 children을 붙여줘서 만들면 될텐데 이번 경우는 그렇지 않습니다. 카테고리 구현형태는 다음을 참고해주세요

2022.10.10 - [[ BE ] 기술] - [BE-기술] 자기참조를 활용한 계층형 카테고리 구현

[BE-기술] 자기참조를 활용한 계층형 카테고리 구현

만약 이 표를  트리 형태로 굳이 표현한다면 아래와 같이 표현이 됩니다.

 

비건과 락토-오보의 부모가.... 2명이네요? 트리구조의 세계에서는 절대 불가능한 형태입니다. 따라서 트리구조로 검색하는 것이 아니라, 다른 방법을 찾아야 했습니다. 일단 요구사항부터 정리를 해보죠.

쿼리 요구사항

1. 회원(Member)은 하나의 채식 유형을 가지고 있다.

2. 제품(Product)은 하나의 채식 유형을 가지고 있다.

3. 채식유형은 총 9가지이다.

4. 먹을 수 있는 식재료가 완전히 겹치는 유형은 함께 조회 가능해야 한다.★

요구사항에 따라 계층형 조회를 하려면 트리 형태가 아니라 채식 유형에 각각 레벨을 부여하여 '유형이 가진 레벨에 따라 조회'하는 것이 나을 것 같습니다.  일단 채식 유형 테이블의 ID를 채식유형 이름으로 두고, 레벨을 부여해보겠습니다.

Vegetarian이라는 테이블에 이름(Vegetarian_name)과 레벨(Vegetarian_level) 두 개의 칼럼을 만들어 준 상태입니다. 위에서 봤던 상하위 구조를 레벨로 표현했습니다. 오보/비건, 폴로/페스코는 같은 레벨에 위치하고 있군요. 총 레벨은 7단계입니다.  채식유형 레벨 관점에서 보자면 오보인 경우 어떤 유형을 조회해야할까요?

 위 이미지를 보면 오보보다 '자신보다 낮은 레벨을 가진 유형과 자기 자신'을 조회해야한다는 사실을 알 수 있습니다. (만약 자신과 같은 레벨까지 조회한다면 락토가 함께 검색이 되어서 원하는 결과가 나오지 않습니다.) 그렇다면 일단 입력된 채식유형을 기준으로 보다 낮은 레벨의 유형과 자기자신을 반환하는 쿼리를 작성해보겠습니다.

SELECT Vegetarian_name FROM Vegetarian WHERE Vegetarian_level < 
(SELECT Vegetarian_level FROM Vegetarian WHERE Vegetarian_name = '오보') 
OR Vegetarian_name = '오보'

 

찬찬히 뜯어보자면,

1. SELECT Vegetarian_name FROM Vegetarian : 채식유형 이름 칼럼을 채식 테이블에서 가져오자

2. WHERE Vegetarian_level <  : 조건은 채식유형레벨이 더 낮은 것으로

3. (SELECT Vegetarian_level FROM Vegetarian WHERE Vegetarian_name = '오보') : 오보(입력 값) 이름을 가진 채식 유형의 레벨보다 낮은 유형을

4. OR Vegetarian_name = '오보' : 입력값(오보) 자체를 포함해서

이 쿼리로 입력값 채식유형(오보)에 따라 어떻게 조회되는지 결과를 보시죠

오보, 비건, 프루테리언 제가 필요한 유형 3가지가 모두 잘 들어왔습니다. 그러나 중요한건 유형검색이 아니라 입력 유형에 따른 제품 검색입니다. 

제품(Product) 테이블부터 만들어야겠네요. 원래는 여러 칼럼이 있지만, 여기서는 필요한 부분만 정리해서 만들었습니다.

각 제품은 하나의 유형을 가지고 있습니다. '오보'라는 채식 유형을 가진 회원에게는 카레라면, 옥수수식빵, 베지밀, 사과만 조회가 되어야하는 쿼리를 만들어야 합니다. (물론 비회원도 유형을 선택할 수 있어야 하기에 파라미터 값으로도 유형을 받을 수 있습니다.) 

일단 제품에서 필요한 쿼리를 차근차근 만들어보겠습니다.

SELECT * FROM Product WHERE VegetarianName

제품 테이블에서 모든 칼럼을 꺼내오는데 조건은 채식유형이름으로 무언가를 걸어줘야 할 것 같습니다. 

이 부분에서 꽤 헤맸는데 조건절에 맞는 채식유형이름이 하나가 아니라 여러 개인 점을 고려해주어야합니다. 예를 들어 오보 유형은 프루테리언, 비건, 오보 3가지가 조건절에 와야 하는 것이죠. 따라서 여기서는 IN 조건문을 사용해줍니다. 

SQL에서 IN은 OR 연산자를 축약해서 만든 형태라고 할 수 있는데요 WHERE절에서 여러 값을 받을 수 있도록 해줍니다. 사용법은 VALUES와 같이 괄호 안에 쉼표로 값을 구분하는 형태입니다.

바로 이 IN 조건문 안에 우리가 기존에 만들었던 유형을 조회하는 쿼리를 서브쿼리로 붙여버리면 제품에서 유형별 조회가 가능해지겠군요! 최종 형태를 보시죠.

SELECT * FROM Product WHERE Vegetarian_name 
IN (SELECT Vegetarian_name FROM Vegetarian WHERE Vegetarian_level < 
(SELECT Vegetarian_level FROM Vegetarian WHERE Vegetarian_name = '오보') 
OR Vegetarian_name = '오보')

다음은 결과값입니다.

 

이제 원하는 쿼리가 완성되었습니다. 이제 이걸 스프링에 JPQL 또는 Query DSL 방식으로 옮겨 담아주기만하면 될 것 같습니다. 다음에는 실제 JSON 형태로 데이터가 옮겨가는 과정을 포스팅해보겠습니다.