본격적으로 NestJS를 사용하는 프로젝트를 진행하면서 복잡한 Query를 구현해야 하는 경우가 생겼습니다. 이에 따라 TypeORM이 제공하는 QueryBuilder를 사용하여 SQL Query를 구현하는 방법에 대해 공부해봤습니다.
QueryBuilder
QueryBuilder는 TypeORM이 제공하는 강력한 기능중 하나로, 우아하면서 편리한 기능으로 SQL Query를 작성하고 실행하여 자동으로 변환된 Entity를 가져올 수 있습니다.
Find, FidnOne, Save와 같은 Repository API는 로직이 간단하거나 사용하기 쉬운 추상화에 적합하다고 생각합니다. 하지만 QueryBuilder는 근본적으로 SQL Query문과 거의 비슷하고(Raw Query정도는 아니지만), 사용자가 정의해서 사용하기 편합니다.
SQL Query의 다양한 Expression을 QueryBuilder로 어떻게 구현하는지 확인해봅시다.
getOrderList
프로젝트에서 구현하게 된
getOrderList가 어떤 Data를 반환해야 하는지 먼저 알아봅시다.
OrderList DB에 저장되어 있는 Order중 제작자, 날짜, 주문상태로 필터링한 후 상품이름을 검색하여 그에 따른 결과를 반환합니다. Return해야하는 Data는
- Order의 특정 Data
- Order와 연결되어 있는 User의 제작자 이름
- Order와 연결되어 있는 OrderSize의 특정 Data
- Order와 연결되어 있는 OrderSizeShipment의 특정 Data
- Order와 연결되어 있는 Product의 특정 Data
- 그 Product와 연결되어 있는 Image
정도로 파악할 수 있습니다. 이제 코드를 하나씩 살펴봅시다.
InnerJoin() & LeftJoin()
먼저 createQueryBuilder를 사용해서 Query문을 사용할 준비를 합니다. createQueryBuilder안의 'order'는 alias를 의미합니다. SQL로 비유하자면 SELECT Order AS order라고 할 수 있습니다.
InnerJoin은 Relationship이 존재하는 두 Table의 정보를 불러옵니다. 대신 Order Instance에 User가 존재하지 않는다면 Order를 Data를 반환하지 않습니다.
반면 LeftJoin은 Order와 연관된 Image가 존재하지 않더라도 Order를 반환합니다. 연관된 Table의 정보가 없을때 우선 Order의 정보는 모두 반환해야 하는 경우는 LeftJoin으로 처리해줍니다. 
그런데 결과를 보면 같은 Order의 정보가 여러번 표출된 것을 알 수 있습니다. 한 Order에 여러 Image가 존재하는데 Join한 테이블을 Select로 따로 지정해주지 않아 나온 결과입니다.
InnerJoinAndSelect() and LeftJoinAndSelect()
Join한 Table의 결과까지 모두 반환하고 싶다면
InnerJoinAndSelect이나leftJoinAndSelect를 사용합니다.
GetRawMany()가 아니라 그냥 GetMany를 사용하면 다음과 같이 LeftJoin의 Image 정보는 빠지는 것을 알 수 있습니다. GetMany는 Entity의 데이터만 가져오는데, CONCAT이나 SUM등 Annotate를 사용하여 Raw Data를 가져와야할 경우는 GetRawMany를 사용합니다.
그냥 InnerJoinAndSelect를 사용하니 불필요한 정보를 너무 많이 가져오는 것 같습니다. Select를 따로 사용하여 필요한 정보만 가져오겠습니다.
SubQuery()
QueryBuilder로 SubQuery 또한 만들수 있습니다. SubQuery는 From, Where, Join에서 사용할 수 있습니다.
OrderList의 경우 연관된 OrderSize의 합이나 ImageNo가 1인 Image를 불러오는 등 Join을 하더라도 특정 Data만 불러오면 되기에 SubQuery를 사용해서 처리를 해보겠습니다.
subQuery명령어를 통해 Query안에서 사용될 수 있음을 정의해주고, getQuery를 이용해 Query 구문인 string 형태로 형변환해줍니다. SubQuery에서 alias로 정의한 parameter들은 다른 부모 Query에서도 그대로 사용할 수 있습니다. 예를들어 Image QueryBuilder에서 image.imgUrl을 URL로 변환해주었는데, 부모쿼리에서 이 SubQuery를 image라고 alias해준다면 image.URL을 사용하면 됩니다.
SUBSTR()
문자열 Column의 output 길이나 index를 조정하고 싶은 경우 SUBSTR을 사용합니다.
orderDate의 output이 TimeStamp(6)으로 설정되어있어 날짜까지만 반환할 수 있도록 SUBSTR을 사용해주었습니다.
Where()
Where을 통해서 다양한 조건에 부합하는 Entity를 찾아낼 수 있습니다.
IN Expression
Order List는 체크한 OrderStatus인 Order만 반환해야합니다. 체크한 OrderStatus를 Query Parameter로 List형태로 받고, List에 있는 OrderStatus에 부합하는 Order만 가져옵니다.
date_format
Date로 필터링을 해야하는 경우, DB에 저장되어 있는 Date는 TIMSTAMP형식으로 저장되어 있어 Format 변환을 해줘야 합니다. Request로 받게되는 Date와 맞게 변환해줍니다.
Like Expression
상품이름 검색을 통해 Order를 불러오고 싶은 경우, Like를 사용합니다.
주의할점은 모든 :은 띄어쓰기 없이 붙여야 한다는 것입니다. %를 양쪽에 붙여 문자열 어디서든 일치한다면 Entity를 반환하도록 합니다.
getOrderList Code
위에서 살펴본 내용을 모두 적용하여 전체 코드와 Integration Test 결과를 봅시다.
대응하는 SQL문은 다음과 같습니다. 확실히 SQL문과 QueryBuilder문법이 거의 비슷한 것을 확인할 수 있습니다. SubQuery를 따로 빼놓긴 했지만 실제로 Query를 실행하는 것은 getRawMany()를 적용했을때 한번입니다.
SELECT `user`.`user_name` AS Produttore, SUBSTR(`order`.`order_date`, 1, 10) AS "Data ordine", SUBSTR(`order`.`etd`, 1,
10) AS "Data consegna promiso", `order`.`order_id` AS "Numero d'ordine", image.URL AS Foto,
CONCAT(`product`.`product_unit`) AS Articolo, quantity.qty AS "Quantità dell'ordine", quantityShip.qtyShip AS "Quantità
di spedizione", quantityShip.qtyArrive AS "Quantità arrivata", `order`.`order_status` AS "Stato dell'ordine" FROM
`scm_order` `order` INNER JOIN `scm_account` `user` ON `user`.`user_id`=`order`.`manufacturer_id` INNER JOIN
`scm_product` `product` ON `product`.`product_id`=`order`.`product_id` LEFT JOIN (SELECT `image`.`product_id` AS
productId, `image`.`img_url` AS URL FROM `scm_product_img` `image` WHERE `image`.`img_no` = 1) `image` ON
image.productId = `order`.`product_id` LEFT JOIN (SELECT `quantity`.`order_id` As orderId, SUM(`quantity`.`qty`) AS qty
FROM `scm_order_size` `quantity` GROUP BY `quantity`.`order_id`) `quantity` ON quantity.orderId = `order`.`order_id`
LEFT JOIN (SELECT `quantityShip`.`order_id` AS orderId, SUM(`quantityShip`.`shipment_qty`) AS qtyShip,
SUM(`quantityShip`.`arrived_qty`) AS qtyArrive FROM `scm_order_size_shipment` `quantityShip` GROUP BY
`quantityShip`.`order_id`) `quantityShip` ON quantityShip.orderId = `order`.`order_id`TIL
Django에서의 ORM은 SQL문에 대해 잘 알지 못한다 하더라도 Filter, Get, Create 등 직관적이고 사용하기 간편했습니다. 하지만 TypeOrm은 기본적으로 제공하는 Repository API가 Find, FindOne, Create, Save등으로 가지수가 적고 실제로 복잡한 Query를 수행하기 위해서 SQL에 대한 전반적인 이해가 필요했습니다.
QueryBuilder를 사용해서 Query를 작성하는 방법을 공부하다보니 자연스레 SQL 문법도 익숙해지고, 좀 더 다양한 방법으로 DB에 접근하는 법을 알게되는 것 같습니다. 블로그에 기재한 코드는 우선 원하는 Entity를 불러오기 위한 기능정도는 만족하지만, 앞으로 많이 Refactoring할 여지가 있어 보입니다. SubQuery를 사용하는 방법도 Join문 안에 함수형태로 작성하면 더 보기 좋을 것 같아 고민하고 있습니다.
또, QueryBuilder를 사용했을 경우의 Unit Test를 작성하기 위해 정말 많은 시간을 구글링하였고, 일단 Chaining function에 대한 Mocking은 해낸것 같은데 이게 정말 맞는 방법인지 아직도 확신이 가지 않고 있습니다. 다음 블로깅은 Transaction과 Unit Test로 해보도록 하겠습니다.
