배달의민족 클론코딩 - 화면 별 MySQL 쿼리 작성하기

배달의 민족 API 짜기에 앞서, 직접 설계한 erd를 바탕으로 몇가지 앱 주요 화면에 대한 쿼리문을 작성해보았다.

설계한 erd는 다른 글에 올려 놓았다.

1. 카테고리 별 가게 리스트

"치킨" 카테고리 선택했을때 보이는 가게 리스트

SELECT S.store_name AS 가게이름,
       S.average_rating AS 별점,
       S.store_description AS 가게설명,
       S.min_price AS 최소주문금액,
       S.delivery_fee AS 배달비,
       CONCAT(S.min_required_time, "~", S.max_required_time) AS 소요시간,
       S.is_takeout_available AS 포장가능여부,
       I.store_image_url AS 이미지url
FROM Stores S
LEFT JOIN Store_images I
ON S.store_id = I.store_id
JOIN Store_categories C
ON S.store_category_id = C.store_category_id
WHERE C.store_category_name = '치킨'

 

Stores 와 Store_images 테이블의 join은 LEFT JOIN 이여야 한다.

-> 이미지 없는 가게도 있을 수 있기 때문

 

2. 메뉴 카테고리별 메뉴 리스트

"순살"카테고리를 선택했을때 보이는 메뉴 리스트

SELECT M.menu_name AS 메뉴이름,
       M.menu_descripton AS 메뉴설명,
       M.menu_price AS 가격,
       M.is_signature_menu AS 대표메뉴여부,
       M.is_popular_menu AS 인기메뉴여부,
       I.menu_image_url AS 이미지url
FROM Stores S, Menu_categories C, Menu M
LEFT JOIN Menu_images I
ON M.menu_id = I.menu_id
WHERE S.store_id = M.store_id && M.menu_category_id = C.menu_category_id && C.menu_category_name = "순살"

 

3. 주문내역

주문번호가 "1"인 주문의 내역을 보여주는 화면

SELECT S.store_name AS 가게이름,
       O.created_at AS 주문일시,
       O.order_id AS 주문번호,
       S.store_telephone AS 가게전화번호,
       GROUP_CONCAT(DISTINCT ME.menu_name, " : ", ME.menu_price) AS "주문메뉴 : 가격",
       GROUP_CONCAT(DISTINCT OP.option_name, " : ", OV.option_value_name) AS "옵션 : 옵션값",
       C.total_price AS 총추문금액,
       C.delivery_fee AS 배달팁,
       O.final_price AS 총결제금액,
       O.pay_type AS 결제방법,
       A.detail_address AS 배달주소,
       U.user_phone_number AS 유저전화번호
       
FROM Orders O, Order_carts C, Order_menu M, Selected_options SO, Stores S, Users U, Menu ME,
     Menu_options OP, Menu_options_value OV, User_address A
     
WHERE O.cart_id = C.cart_id && C.cart_id = M.cart_id && M.order_menu_id = SO.order_menu_id &&
      O.store_id = S.store_id && O.user_id = U.user_id && M.menu_id = ME.menu_id &&
      SO.option_id = OP.option_id && SO.option_value_id = OV.option_value_id &&
      O.delivery_address_id = A.address_id && O.order_id = 1

선택한 옵션들의 경우, 결과가 여러 줄로 중복해서 보여지기 때문에 1:N 관계인 값들은 group concat으로 이어붙여서 한 컬럼에 넣어주었다. 해당 화면에서는 주문메뉴가 한 가지이지만, 주문 메뉴가 여러개인 경우도 마찬가지로 처리할 수 있다.

 

4. 내가 쓴 리뷰 

유저 id가 "1"인 사람이 작성한 리뷰목록을 조회한 화면

SELECT @count:=@count+1 AS 리뷰번호,
       S.store_name AS 가게이름,
       R.rating AS 별점,
       O.created_at AS 주문일시,
       I.review_image_url AS 리뷰사진url,
       R.review_text AS 리뷰내용,
       GROUP_CONCAT(DISTINCT ME.menu_name) AS 주문메뉴,
       R.owner_review_comment AS 사장님댓글

FROM Stores S, Orders O, Order_carts C,
     Order_menu M, Menu ME, (SELECT @count:=0) TMP, Review R
LEFT JOIN Review_images I
ON I.review_id = R.review_id
WHERE R.review_id = I.review_id && R.store_id = S.store_id &&
      R.order_id = O.order_id && O.cart_id = C.cart_id &&
      C.cart_id = M.cart_id && M.menu_id = ME.menu_id && R.user_id = 1
GROUP BY R.review_id

총 리뷰 개수를 파악 할 수 있도록 count로 인덱스를 달아주었다.

 

5.  내 쿠폰함

유저 id가 "1"인 사람이 보유한 쿠폰 리스트를 조회한 화면

SELECT @count:=@count+1 AS 쿠폰번호,
       C.discount_price AS 할인가격,
       S.is_takeout_available AS 포장가능여부,
       C.coupon_name AS 쿠폰이름,
       C.min_price AS 최소주문금액,
       CONCAT(DATE_FORMAT(C.created_at, "%Y.%m.%d"), " - ",
           DATE_FORMAT(C.expire_at, "%Y.%m.%d")) AS 사용기한

FROM Coupons C, User_coupons U, Stores S, (SELECT @count:=0) TMP
WHERE C.coupon_id = U.coupon_id && C.store_id = S.store_id && U.user_id = 1

timestamp 데이터 포맷을 맞춰서 출력해주었다.