rawquery정리

2차프로젝트 중 상품 메인페이지, 상세페이지, 필터링, 정렬, 페이지네이션 등
기능을 한 api에 정리하고싶어서 처음에 한번에 쭈루루루루룩 다 썼다.

그러다보니 레이어드 패턴 중 api 는 하나지만 코드 양이 어마어마했다.

그래서 줄여보기로 했다.

1차

const { appDataSource } = require("./appDataSource");
const {
  whereClause,
  andClause,
  betweenClause,
} = require("../utils/queryModules");

const getProductList = async (
  oil,
  year,
  sunroof,
  color,
  parkingSensor,
  backCamera,
  navi,
  heatingSeat,
  coolingSeat,
  smartKey,
  leatherSeat,
  size,
  type,
  listType,
  brand,
  minMileage,
  maxMileage,
  minPrice,
  maxPrice,
  offset,
  limit,
  sort
) => {
  const sortMethod = Object.freeze({
    cheap: "p.price ASC",
    expensive: "p.price DESC",
    lastest: "p.created_at DESC",
    oldest: "p.created_at ASC",
  });
  const whereClauseBrand = brand ? `WHERE c.brand_id = ${brand}` : "";
  const whereClauseType = type ? `WHERE c.type_id = ${type}` : "";
  const andType = type ? `AND c.type= ${type}` : "";
  const andListType = listType ? `WHERE c.type= ${listType}` : "";
  const andSize = size ? `AND c.size_id = ${size}` : "";
  const andOil = oil ? `AND p.oil = ${oil}` : "";
  const andYear = year ? `AND p.year = ${year}` : "";
  const andSunroof = sunroof ? `AND po.sunroof = ${sunroof}` : "";
  const andColor = color ? `AND po.color = ${color}` : "";
  const andParkingSensor = parkingSensor
    ? `AND po.parkingsensor = ${parkingSensor}`
    : "";
  const andBackCamera = backCamera ? `AND po.backcamera = ${backCamera}` : "";
  const andNavi = navi ? `AND po.heatingseat = ${heatingSeat}` : "";
  const andCoolingSeat = coolingSeat
    ? `AND po.coolingseat =${coolingSeat}`
    : "";
  const andSmartKey = smartKey ? `AND po.smartkey = ${smartKey}` : "";
  const andLeatherSeat = leatherSeat
    ? `AND po.leatherseat = ${leatherSeat}`
    : "";
  const andMileage = maxMileage
    ? `AND p.mileage BETWEEN ${minMileage} AND ${maxMileage}`
    : "";
  const andPrice = maxPrice
    ? `AND p.price BETWEEN ${minPrice} AND ${maxPrice}`
    : "";

  return await appDataSource.query(
    `SELECT 
        c.id        AS carId,
        c.name      AS carName,
        c.thumbnail AS mainThumbnailImage,
        c.type      AS type,
        p.price     AS price,
        p.thumbnail AS listThumbnailImage,
        b.name      AS brandName
      FROM 
        cars c
      JOIN brands b           ON c.brand_id = b.id
      JOIN products p         ON p.car_id = c.id
      JOIN product_options po ON po.product_id = p.id
      ${whereClauseBrand}
      ${whereClauseType}
      ${andType}
      ${andListType}
      ${andSize}
      ${andOil}
      ${andYear}
      ${andSunroof}
      ${andColor}
      ${andParkingSensor}
      ${andBackCamera}
      ${andNavi}
      ${andCoolingSeat}
      ${andSmartKey}
      ${andLeatherSeat}
      ${andMileage}
      ${andPrice}
      ORDER BY ${sortMethod[sort]}
      LIMIT ${limit} OFFSET ${offset} 
      `
  );
};

module.exports = { getProductList };

뭐가 디게 많다.

그래서 2차로 조금 줄였다.

const { appDataSource } = require("./appDataSource");
const {
  whereClause,
  andClause,
  betweenClause,
  forClause,
} = require("../utils/queryModules");

const getProductList = async (
  oil,
  minYear,
  maxYear,
  sunroof,
  color,
  parkingSensor,
  backCamera,
  navi,
  heatingSeat,
  coolingSeat,
  smartKey,
  leatherSeat,
  size,
  type,
  brand,
  minMileage,
  maxMileage,
  minPrice,
  maxPrice,
  offset,
  limit,
  sort
) => {
  const sortMethod = Object.freeze({
    cheap: "p.price ASC",
    expensive: "p.price DESC",
    lastest: "p.created_at DESC",
    oldest: "p.created_at ASC",
  });
  const whereBrand = whereClause(brand, "c.brand_id");
  const andType = forClause([type, size], ["c.type", "c.size"]);
  const andType = andClause(type, "c.type");
  const andSize = andClause(size, "c.size_id");
  const andOil = andClause(oil, "p.oil");
  const andSunroof = andClause(sunroof, "po.sunroof");
  const andColor = andClause(color, "po.color");
  const andParkingSensor = andClause(parkingSensor, "po.parkingsensor");
  const andBackCamera = andClause(backCamera, "po.backcamera");
  const andNavi = andClause(navi, "po.heatingseat");
  const andHeatingSeat = andClause(heatingSeat, "po.heatingseat");
  const andCoolingSeat = andClause(coolingSeat, "po.coolingseat");
  const andSmartKey = andClause(smartKey, "po.smartkey");
  const andLeatherSeat = andClause(leatherSeat, "po.leatherseat");
  const andMileage = betweenClause(minMileage, maxMileage, "p.mileage");
  const andPrice = betweenClause(minPrice, maxPrice, "p.price");
  const andYear = betweenClause(minYear, maxYear, "p.year");

  return await appDataSource.query(
    `SELECT
        c.id        AS carId,
        c.name      AS carName,
        c.thumbnail AS mainThumbnailImage,
        c.type      AS type,
        p.price     AS price,
        p.thumbnail AS listThumbnailImage,
        b.name      AS brandName
      FROM 
        cars c
      JOIN brands b           ON c.brand_id = b.id
      JOIN products p         ON p.car_id = c.id
      JOIN product_options po ON po.product_id = p.id
      ${whereBrand}
      ${andType}
      ${andOil}
      ${andYear}
      ${andSunroof}
      ${andColor}
      ${andParkingSensor}
      ${andBackCamera}
      ${andHeatingSeat}
      ${andNavi}
      ${andCoolingSeat}
      ${andSmartKey}
      ${andLeatherSeat}
      ${andMileage}
      ${andPrice}
      ORDER BY ${sortMethod[sort]}
      LIMIT ${limit} OFFSET ${offset} 
      `
  );
};

module.exports = { getProductList };

그런데도 많다

그래서 반복되는걸 없앴고 따로 뺐다.

querymodule.js

const forClause = (a, b) => {
  let aList = [];
  let bList = [];
  let result = "";
  let arr = [];

  for (i = 0; i < a.length; i++) {
    if (!a[i]) arr.push(a[i]);

    aList.push(a[i]);
  }
  for (j = 0; j < b.length; j++) {
    if (!b[j]) arr.push(b[j]);

    bList.push(b[j]);
  }
  for (k = 0; k < aList.length; k++) {
    result += andClause(aList[k], bList[k]);
  }
  return result;
};

const andClause = (params, params2) => {
  return params ? `AND ${params2} = ${params} ` : "";
};

const betweenClause = (params, params2, params3) => {
  return params2 ? `AND ${params3} BETWEEN ${params} AND ${params2} ` : "";
};

module.exports = { betweenClause, forClause };

productDao.js
const { appDataSource } = require("./appDataSource");
const { betweenClause, forClause } = require("../utils/queryModules");

const getProductList = async (offset, limit, sort, filterOptions) => {
  const sortMethod = Object.freeze({
    cheap: "p.price ASC",
    expensive: "p.price DESC",
    lastest: "p.created_at DESC",
    oldest: "p.created_at ASC",
  });

  const andType = forClause(
    [
      filterOptions.brand,
      filterOptions.oil,
      filterOptions.sunroof,
      filterOptions.color,
      filterOptions.parkingsensor,
      filterOptions.backcamera,
      filterOptions.navi,
      filterOptions.heatingseat,
      filterOptions.coolingseat,
      filterOptions.smartkey,
      filterOptions.leatherseat,
      filterOptions.size,
      filterOptions.type,
    ],
    [
      "c.brand_id",
      "p.oil",
      "po.sunroof",
      "po.color",
      "po.parkingsensor",
      "po.backcamera",
      "po.navi",
      "po.heatingseat",
      "po.coolingseat",
      "po.smartkey",
      "po.leatherseat",
      "c.size",
      "c.type",
    ]
  );
  const andBetweenMileage = betweenClause(
    filterOptions.minMileage,
    filterOptions.maxMileage,
    "p.mileage"
  );
  const andBetweenPrice = betweenClause(
    filterOptions.minPrice,
    filterOptions.maxPrice,
    "p.price"
  );
  const andBetweenYear = betweenClause(
    filterOptions.minYear,
    filterOptions.maxYear,
    "p.year"
  );

  return await appDataSource.query(
    `SELECT
        c.id        AS carId,
        c.name      AS carName,
        c.thumbnail AS mainThumbnailImage,
        c.type      AS type,
        p.price     AS price,
        p.thumbnail AS listThumbnailImage,
        b.name      AS brandName
      FROM 
        cars c
      JOIN brands b           ON c.brand_id = b.id
      JOIN products p         ON p.car_id = c.id
      JOIN product_options po ON po.product_id = p.id
      LEFT JOIN biddings bd        ON bd.product_id = p.id
      LEFT JOIN orders o           ON o.bidding_id = bd.id
      WHERE o.order_status_id IS NULL
      ${andType}
      ${andBetweenYear}
      ${andBetweenMileage}
      ${andBetweenPrice}
      ORDER BY ${sortMethod[sort]}
      LIMIT ${limit} OFFSET ${offset} 
      `
  );
};

module.exports = { getProductList };

더줄일수있지만, 일단 남은 프로젝트를 위해 킵해놓고 다른 api를 시작했다.

나중에 디벨롭 더 시켜서 더 줄여보자