TypeOrm에서 findOption의 order를 이용시 skip과 take의 paginate 오류

await this.repository.findAndCount({
        select: {
            id: true,
            groupId: true,
            scheduleImage: true,
            scheduleName: true,
            startPeriod: true,
            endPeriod: true,
            updatedAt: true,
            schedulePeriods: {
                id: true,
                period: true,
                startTime: true,
                endTime: true,
                tourisms: {
                    id: true,
                    contentId: true,
                    stayTime: true,
                    tourismImage: true,
                    title: true,
                    position: true,
                },
            },
        },
        where: {
            memberId: memberId,
        },
        relations: {
            schedulePeriods: {
                tourisms: true,
            },
        },
        order: {
            updatedAt: 'desc',
            schedulePeriods: {
                period: 'ASC',
             tourisms: {
                position: 'ASC',
             },
            },
        },
        take,
        skip,
});

 

relation의 schedulePeriods의 period 기준으로 오름차순으로 가져오고, toruisms를 position을 기준으로 오름 차순으로 가져오는 order 옵션을 주었을때, take와 skip을 통한 paginate하지 않았을때는 정상적으로 잘 가져왔지만, take skip으로 paginate 했을 때 완전 다른 결과값을 가져왔다.

 

...

order: {
    updatedAt: 'desc',
    schedulePeriods: {
        period: 'ASC',
     tourisms: {
        position: 'ASC',
     },
    },
},
..

 

// 해당 order옵션에 따른 select 쿼리

SELECT 
DISTINCT "distinctAlias"."ScheduleEntity_id" AS "ids_ScheduleEntity_id",
"distinctAlias"."ScheduleEntity_updatedAt"
 ,"distinctAlias"."ScheduleEntity__ScheduleEntity_schdulePeriods_period" 
FROM (SELECT "ScheduleEntity"."id" AS "ScheduleEntity_id", "ScheduleEntity"."updatedAt" AS "ScheduleEntity_updatedAt",
"ScheduleEntity"."groupId" AS "ScheduleEntity_groupId", 
"ScheduleEntity"."scheduleName" AS "ScheduleEntity_scheduleName",
"ScheduleEntity"."scheduleImage" AS "ScheduleEntity_scheduleImage", 
"ScheduleEntity"."startPeriod" AS "ScheduleEntity_startPeriod", 
"ScheduleEntity"."endPeriod" AS "ScheduleEntity_endPeriod",
"ScheduleEntity__ScheduleEntity_schdulePeriods"."id" AS "ScheduleEntity__ScheduleEntity_schdulePeriods_id",
"ScheduleEntity__ScheduleEntity_schdulePeriods"."period" AS "ScheduleEntity__ScheduleEntity_schdulePeriods_period", "ScheduleEntity__ScheduleEntity_schdulePeriods"."startTime" AS "ScheduleEntity__ScheduleEntity_schdulePeriods_startTime",
"ScheduleEntity__ScheduleEntity_schdulePeriods"."endTime" AS "ScheduleEntity__ScheduleEntity_schdulePeriods_endTime", "f700d7700ecabe5aed2180015402288c42873cd3"."id" AS "f700d7700ecabe5aed2180015402288c42873cd3_id",
"f700d7700ecabe5aed2180015402288c42873cd3"."contentId" AS "f700d7700ecabe5aed2180015402288c42873cd3_contentId", "f700d7700ecabe5aed2180015402288c42873cd3"."stayTime" AS "f700d7700ecabe5aed2180015402288c42873cd3_stayTime",
"f700d7700ecabe5aed2180015402288c42873cd3"."tourismImage" AS "f700d7700ecabe5aed2180015402288c42873cd3_tourismImage", "f700d7700ecabe5aed2180015402288c42873cd3"."title" AS "f700d7700ecabe5aed2180015402288c42873cd3_title", "f700d7700ecabe5aed2180015402288c42873cd3"."position" AS "f700d7700ecabe5aed2180015402288c42873cd3_position" FROM "fam_schedule" "ScheduleEntity" LEFT JOIN "fam_tourism_period" "ScheduleEntity__ScheduleEntity_schdulePeriods" ON "ScheduleEntity__ScheduleEntity_schdulePeriods"."scheduleId"="ScheduleEntity"."id"  
LEFT JOIN "fam_tourism" "f700d7700ecabe5aed2180015402288c42873cd3" ON "f700d7700ecabe5aed2180015402288c42873cd3"."periodId"="ScheduleEntity__ScheduleEntity_schdulePeriods"."id"
WHERE ("ScheduleEntity"."memberId" = '410b7202-660a-4423-a6c3-6377857241cc' )) "distinctAlias"
ORDER BY "distinctAlias"."ScheduleEntity_updatedAt" DESC, "distinctAlias"."ScheduleEntity__ScheduleEntity_schdulePeriods_period" ASC, "ScheduleEntity_id" ASC LIMIT 3 OFFSET 3

 

[ 해당 쿼리에 대한 결과] - 잘못된 id값들을 가져온다.

aa8eb7bf-d4e6-4c0d-802b-b64abb3ba155 
99feca37-78cd-45ab-8270-198f45a67601 
84831b25-404e-4675-871c-6534d9b8633e

 

 

...

order: {
    updatedAt: 'desc',
 
},
..

 

// 해당 order옵션에 따른 select 쿼리
SELECT 
DISTINCT "distinctAlias"."ScheduleEntity_id" AS "ids_ScheduleEntity_id", 
"distinctAlias"."ScheduleEntity_updatedAt" 
FROM (SELECT "ScheduleEntity"."id" AS "ScheduleEntity_id", "ScheduleEntity"."updatedAt" AS "ScheduleEntity_updatedAt", "ScheduleEntity"."groupId" AS "ScheduleEntity_groupId", "ScheduleEntity"."scheduleName" AS "ScheduleEntity_scheduleName", "ScheduleEntity"."scheduleImage" AS "ScheduleEntity_scheduleImage", "ScheduleEntity"."startPeriod" AS "ScheduleEntity_startPeriod", "ScheduleEntity"."endPeriod" AS "ScheduleEntity_endPeriod", "ScheduleEntity__ScheduleEntity_schdulePeriods"."id" AS "ScheduleEntity__ScheduleEntity_schdulePeriods_id", "ScheduleEntity__ScheduleEntity_schdulePeriods"."period" AS "ScheduleEntity__ScheduleEntity_schdulePeriods_period", "ScheduleEntity__ScheduleEntity_schdulePeriods"."startTime" AS "ScheduleEntity__ScheduleEntity_schdulePeriods_startTime", "ScheduleEntity__ScheduleEntity_schdulePeriods"."endTime" AS "ScheduleEntity__ScheduleEntity_schdulePeriods_endTime", "f700d7700ecabe5aed2180015402288c42873cd3"."id" AS "f700d7700ecabe5aed2180015402288c42873cd3_id", "f700d7700ecabe5aed2180015402288c42873cd3"."contentId" AS "f700d7700ecabe5aed2180015402288c42873cd3_contentId", "f700d7700ecabe5aed2180015402288c42873cd3"."stayTime" AS "f700d7700ecabe5aed2180015402288c42873cd3_stayTime", "f700d7700ecabe5aed2180015402288c42873cd3"."tourismImage" AS "f700d7700ecabe5aed2180015402288c42873cd3_tourismImage", "f700d7700ecabe5aed2180015402288c42873cd3"."title" AS "f700d7700ecabe5aed2180015402288c42873cd3_title", "f700d7700ecabe5aed2180015402288c42873cd3"."position" AS "f700d7700ecabe5aed2180015402288c42873cd3_position" FROM "fam_schedule" "ScheduleEntity" LEFT JOIN "fam_tourism_period" "ScheduleEntity__ScheduleEntity_schdulePeriods" ON "ScheduleEntity__ScheduleEntity_schdulePeriods"."scheduleId"="ScheduleEntity"."id"  LEFT JOIN "fam_tourism" "f700d7700ecabe5aed2180015402288c42873cd3" ON "f700d7700ecabe5aed2180015402288c42873cd3"."periodId"="ScheduleEntity__ScheduleEntity_schdulePeriods"."id" WHERE ("ScheduleEntity"."memberId" = '410b7202-660a-4423-a6c3-6377857241cc')) "distinctAlias" ORDER BY "distinctAlias"."ScheduleEntity_updatedAt" DESC, "ScheduleEntity_id" ASC 
LIMIT 3 OFFSET 3

 

[ 해당 쿼리에 대한 결과] - 정확한 id값들을 가져온다.

aa8eb7bf-d4e6-4c0d-802b-b64abb3ba155 
99feca37-78cd-45ab-8270-198f45a67601 
84831b25-404e-4675-871c-6534d9b8633e

 

내가 원하는 쿼리는 사실 schedulePeriods테이블에서 select 할때 period로 order by 한것들 from절에서 가져오고 그걸 join하는걸 원했지만 실제 쿼리에선 그렇게 동작하지 않는것 같다.

 

결론적으로 해결한 방법은 필수적으로 필요한 값만 paginate 하면 되기 때문에 불필요한 select절을 수정 해서 해결 했다.

 

추후에 prisma와 typeorm의 예시 테이블들을 만들어서 order by를 어떻게 요청 하는지 알아봐야겠다.