🚨Issue occuring

---

불필요한 DB접근과 특정 컬럼 조회를 하지 못함

기존코드

  async filterPostsByRegion(
    id: number,
    page: number,
  ): Promise<DetectivePost[]> {
    const pageSize = 20;
    const skip = (page - 1) * pageSize;
    const posts = await this.detectivePostRepo.find({
      where: { regionId: id },
      relations: ['detective', 'detective.user'],
      skip: skip,
      take: pageSize,
      select: ['id', 'categoryId', 'regionId', 'detective'],
    });

    const totalCount = await this.detectivePostRepo.count({
      where: { regionId: id },
    });

    return { posts, totalCount };
  }

• find를 사용하여 조건에 만족하는 모든 데이터를 반환하도록 함. -> 관계 설정 및 필드 선택으로 특정 정보만 조회했지만 불필요한 정보까지 출력이 됨
• 데이터양의 많을 시 조회될 post의 갯수를 20으로 조정

query: SELECT DISTINCT "distinctAlias"."DetectivePost_id" AS "ids_DetectivePost_id" FROM (SELECT "DetectivePost"."id" AS "DetectivePost_id", "DetectivePost"."region_id" AS "DetectivePost_region_id", "DetectivePost"."category_id" AS "DetectivePost_category_id", "DetectivePost__DetectivePost_detective"."id" AS "DetectivePost__DetectivePost_detective_id", "DetectivePost__DetectivePost_detective"."user_id" AS "DetectivePost__DetectivePost_detective_user_id", "DetectivePost__DetectivePost_detective"."office_id" AS "DetectivePost__DetectivePost_detective_office_id", "DetectivePost__DetectivePost_detective"."gender" AS "DetectivePost__DetectivePost_detective_gender", "DetectivePost__DetectivePost_detective"."position" AS "DetectivePost__DetectivePost_detective_position", "DetectivePost__DetectivePost_detective"."business_registration_file_id" AS "1a4d13df64111426c37a3d60a69e75a306b7e236", "DetectivePost__DetectivePost_detective"."created_at" AS "DetectivePost__DetectivePost_detective_created_at", "DetectivePost__DetectivePost_detective"."updated_at" AS "DetectivePost__DetectivePost_detective_updated_at", "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."id" AS "212ba2629108fdf5a6570c042ac3d7c2307b1b81_id", "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."name" AS "212ba2629108fdf5a6570c042ac3d7c2307b1b81_name", "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."email" AS "212ba2629108fdf5a6570c042ac3d7c2307b1b81_email", "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."nickname" AS "212ba2629108fdf5a6570c042ac3d7c2307b1b81_nickname", "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."phone_number" AS "212ba2629108fdf5a6570c042ac3d7c2307b1b81_phone_number", "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."password" AS "212ba2629108fdf5a6570c042ac3d7c2307b1b81_password", "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."created_at" AS "212ba2629108fdf5a6570c042ac3d7c2307b1b81_created_at", "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."updated_at" AS "212ba2629108fdf5a6570c042ac3d7c2307b1b81_updated_at" FROM "detective_post" "DetectivePost" LEFT JOIN "detective" "DetectivePost__DetectivePost_detective" ON "DetectivePost__DetectivePost_detective"."id"="DetectivePost"."detective_id"  LEFT JOIN "user" "212ba2629108fdf5a6570c042ac3d7c2307b1b81" ON "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."id"="DetectivePost__DetectivePost_detective"."user_id" WHERE (("DetectivePost"."region_id" = $1))) "distinctAlias" ORDER BY "DetectivePost_id" ASC LIMIT 20 -- PARAMETERS: [17]
query: SELECT "DetectivePost"."id" AS "DetectivePost_id", "DetectivePost"."region_id" AS "DetectivePost_region_id", "DetectivePost"."category_id" AS "DetectivePost_category_id", "DetectivePost__DetectivePost_detective"."id" AS "DetectivePost__DetectivePost_detective_id", "DetectivePost__DetectivePost_detective"."user_id" AS "DetectivePost__DetectivePost_detective_user_id", "DetectivePost__DetectivePost_detective"."office_id" AS "DetectivePost__DetectivePost_detective_office_id", "DetectivePost__DetectivePost_detective"."gender" AS "DetectivePost__DetectivePost_detective_gender", "DetectivePost__DetectivePost_detective"."position" AS "DetectivePost__DetectivePost_detective_position", "DetectivePost__DetectivePost_detective"."business_registration_file_id" AS "1a4d13df64111426c37a3d60a69e75a306b7e236", "DetectivePost__DetectivePost_detective"."created_at" AS "DetectivePost__DetectivePost_detective_created_at", "DetectivePost__DetectivePost_detective"."updated_at" AS "DetectivePost__DetectivePost_detective_updated_at", "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."id" AS "212ba2629108fdf5a6570c042ac3d7c2307b1b81_id", "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."name" AS "212ba2629108fdf5a6570c042ac3d7c2307b1b81_name", "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."email" AS "212ba2629108fdf5a6570c042ac3d7c2307b1b81_email", "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."nickname" AS "212ba2629108fdf5a6570c042ac3d7c2307b1b81_nickname", "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."phone_number" AS "212ba2629108fdf5a6570c042ac3d7c2307b1b81_phone_number", "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."password" AS "212ba2629108fdf5a6570c042ac3d7c2307b1b81_password", "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."created_at" AS "212ba2629108fdf5a6570c042ac3d7c2307b1b81_created_at", "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."updated_at" AS "212ba2629108fdf5a6570c042ac3d7c2307b1b81_updated_at" FROM "detective_post" "DetectivePost" LEFT JOIN "detective" "DetectivePost__DetectivePost_detective" ON "DetectivePost__DetectivePost_detective"."id"="DetectivePost"."detective_id"  LEFT JOIN "user" "212ba2629108fdf5a6570c042ac3d7c2307b1b81" ON "212ba2629108fdf5a6570c042ac3d7c2307b1b81"."id"="DetectivePost__DetectivePost_detective"."user_id" WHERE ( (("DetectivePost"."region_id" = $1)) ) AND ( "DetectivePost"."id" IN ($2, $3, $4, $5, $6, $7) ) -- PARAMETERS: [17,"21","24","35","36","37","38"]
query: SELECT COUNT(1) AS "cnt" FROM "detective_post" "DetectivePost" WHERE (("DetectivePost"."region_id" = $1)) -- PARAMETERS: [17]

💦Tried & 💡Solve

---

async filterPostsByRegion(
    id: number,
    page: number,
  ): Promise<{ posts: Partial<DetectivePost>[]; totalCount: number }> {
    const pageSize = 20;
    const skip = (page - 1) * pageSize;

    const [posts, totalCount] = await this.detectivePostRepo
      .createQueryBuilder('detectivePost')
      .leftJoin('detectivePost.detective', 'detective')
      .leftJoin('detective.user', 'user')
      .select([
        'detectivePost.id',
        'detectivePost.categoryId',
        'detectivePost.regionId',
        'user.name',
      ])
      .where('detectivePost.regionId = :id', { id })
      .skip(skip)
      .take(pageSize)
      .getManyAndCount();

    return { posts, totalCount };
  }

query: SELECT DISTINCT "distinctAlias"."detectivePost_id" AS "ids_detectivePost_id" FROM (SELECT "detectivePost"."id" AS "detectivePost_id", "detectivePost"."region_id" AS "detectivePost_region_id", "detectivePost"."category_id" AS "detectivePost_category_id", "user"."name" AS "user_name" FROM "detective_post" "detectivePost" LEFT JOIN "detective" "detective" ON "detective"."id"="detectivePost"."detective_id"  LEFT JOIN "user" "user" ON "user"."id"="detective"."user_id" WHERE "detectivePost"."region_id" = $1) "distinctAlias" ORDER BY "detectivePost_id"
 ASC LIMIT 20 -- PARAMETERS: [17]
query: SELECT "detectivePost"."id" AS "detectivePost_id", "detectivePost"."region_id" AS "detectivePost_region_id", "detectivePost"."category_id" AS "detectivePost_category_id", "user"."name" AS "user_name" FROM "detective_post" "detectivePost" LEFT JOIN "detective" "detective" ON "detective"."id"="detectivePost"."detective_id"  LEFT JOIN "user" "user" ON "user"."id"="detective"."user_id" WHERE ( "detectivePost"."region_id" = $1 ) AND ( "detectivePost"."id" IN ($2, $3, $4, $5, $6, $7) ) -- PARAMETERS: [17,"21","24","35","36","37","38"]
query: SELECT COUNT(DISTINCT("detectivePost"."id")) AS "cnt" FROM "detective_post" "detectivePost" LEFT JOIN "detective" "detective" ON "detective"."id"="detectivePost"."detective_id"  LEFT JOIN "user" "user" ON "user"."id"="detective"."user_id" WHERE "detectivePost"."region_id" = $1 -- PARAMETERS: [17]

1. leftJoinAndSelect가 아닌 leftJoin을 사용하여 특정 필드 조회 후 반환하도록 함.
2. getManyAndCount을 사용하여 페이지네이션 시 하나의 쿼리로 해결
3. 제네릭 타입 사용으로 안정적인 데이터 반환

📃What I learned new

---

1. QueryBuilder: 조금 더 복잡한 쿼리를 조회할 때 사용!
2. getManyAndCount : 한 번의 쿼리로 총 개수와 데이터를 함께 가져와 효율적!
3. 제네릭 타입 : 데이터의 일관성 유지할 수 있어 좋음

🤔Realization

---

typeORM 기본 메서드인 find, findOne 등 접근성은 편리하지만 복잡한 쿼리를 다룰 땐 queryBuilder를 사용했어야 했고, raw 쿼리에 대해 공부를 해봐야겠다.

해야할 일
1. 모니터링 및 로깅으로 PLG스택 추가
2. 인덱싱을 추가해 비교해본 뒤 코드 수정하기