Missing from clause entry in version 8.3.1
Alsdrouf opened this issue · 5 comments
Alsdrouf commented
With nestjs-paginate 8.3.0
[Nest] 601 - 09/26/2023, 2:33:46 PM DEBUG [LoggingInterceptor] Start /fleets(GET) from user 928bf625-ed3a-44c6-97a8-acf002c04dbe
2023-09-26T12:33:46.517216680Z query: SELECT DISTINCT "distinctAlias"."__root_id" AS "ids___root_id", "distinctAlias"."__root_id" FROM (SELECT "__root"."id" AS "__root_id", "__root"."name" AS "__root_name", "__root"."color" AS "__root_color", "__root"."caution" AS "__root_caution", "__root"."geoJson" AS "__root_geoJson", "__root"."stripeProductId" AS "__root_stripeProductId", "__root"."createdAt" AS "__root_createdAt", "__root"."updatedAt" AS "__root_updatedAt", "__root"."deletedAt" AS "__root_deletedAt", "__root"."operatorId" AS "__root_operatorId", "__root_vehicles_rel"."id" AS "__root_vehicles_rel_id", "__root_vehicles_rel"."serialNumber" AS "__root_vehicles_rel_serialNumber", "__root_vehicles_rel"."status" AS "__root_vehicles_rel_status", "__root_vehicles_rel"."deviceImei" AS "__root_vehicles_rel_deviceImei", "__root_vehicles_rel"."type" AS "__root_vehicles_rel_type", "__root_vehicles_rel"."isOutOfBound" AS "__root_vehicles_rel_isOutOfBound", "__root_vehicles_rel"."otherData" AS "__root_vehicles_rel_otherData", "__root_vehicles_rel"."createdAt" AS "__root_vehicles_rel_createdAt", "__root_vehicles_rel"."updatedAt" AS "__root_vehicles_rel_updatedAt", "__root_vehicles_rel"."deletedAt" AS "__root_vehicles_rel_deletedAt", "__root_vehicles_rel"."positionId" AS "__root_vehicles_rel_positionId", "__root_vehicles_rel"."currentTravelId" AS "__root_vehicles_rel_currentTravelId", "__root_vehicles_rel"."currentRentalId" AS "__root_vehicles_rel_currentRentalId", "__root_vehicles_rel"."operatorId" AS "__root_vehicles_rel_operatorId", "__root_vehicles_rel"."stationId" AS "__root_vehicles_rel_stationId", "__root_vehicles_rel"."agencyId" AS "__root_vehicles_rel_agencyId", "__root_vehicles_rel"."modelId" AS "__root_vehicles_rel_modelId", "__root_vehicles_rel"."fleetId" AS "__root_vehicles_rel_fleetId", "__root_vehicles_rel"."userId" AS "__root_vehicles_rel_userId", "__root_vehicles_rel_position_rel"."id" AS "__root_vehicles_rel_position_rel_id", "__root_vehicles_rel_position_rel"."latitude" AS "__root_vehicles_rel_position_rel_latitude", "__root_vehicles_rel_position_rel"."longitude" AS "__root_vehicles_rel_position_rel_longitude", "__root_vehicles_rel_position_rel"."createdAt" AS "__root_vehicles_rel_position_rel_createdAt", "__root_vehicles_rel_position_rel"."vehicleId" AS "__root_vehicles_rel_position_rel_vehicleId", "__root_vehicles_rel_position_rel"."deviceImei" AS "__root_vehicles_rel_position_rel_deviceImei", "__root_vehicles_rel_position_rel"."travelId" AS "__root_vehicles_rel_position_rel_travelId" FROM "fleet" "__root" LEFT JOIN "vehicle" "__root_vehicles_rel" ON "__root_vehicles_rel"."fleetId"="__root"."id" AND ("__root_vehicles_rel"."deletedAt" IS NULL) LEFT JOIN "position" "__root_vehicles_rel_position_rel" ON "__root_vehicles_rel_position_rel"."id"="__root_vehicles_rel"."positionId" WHERE ( ("__root"."operatorId" = $1) ) AND ( "__root"."deletedAt" IS NULL )) "distinctAlias" ORDER BY "distinctAlias"."__root_id" ASC, "__root_id" ASC LIMIT 20 -- PARAMETERS: ["9430f2fb-8225-444f-8b73-59b5de43ec68"]
2023-09-26T12:33:46.526077649Z query: SELECT COUNT(DISTINCT("__root"."id")) AS "cnt" FROM "fleet" "__root" LEFT JOIN "vehicle" "__root_vehicles_rel" ON "__root_vehicles_rel"."fleetId"="__root"."id" AND ("__root_vehicles_rel"."deletedAt" IS NULL) LEFT JOIN "position" "__root_vehicles_rel_position_rel" ON "__root_vehicles_rel_position_rel"."id"="__root_vehicles_rel"."positionId" WHERE ( ("__root"."operatorId" = $1) ) AND ( "__root"."deletedAt" IS NULL ) -- PARAMETERS: ["9430f2fb-8225-444f-8b73-59b5de43ec68"]
2023-09-26T12:33:46.529670132Z [Nest] 601 - 09/26/2023, 2:33:46 PM DEBUG [LoggingInterceptor] Object:
2023-09-26T12:33:46.529702073Z {
2023-09-26T12:33:46.529705720Z "url": "/fleets",
2023-09-26T12:33:46.529708716Z "method": "GET",
2023-09-26T12:33:46.529711331Z "statusCode": 200,
2023-09-26T12:33:46.529713164Z "timeTaken": "35ms",
2023-09-26T12:33:46.529714988Z "ip": "::ffff:172.27.0.1",
2023-09-26T12:33:46.529716821Z "userId": "928bf625-ed3a-44c6-97a8-acf002c04dbe"
2023-09-26T12:33:46.529737821Z }
2023-09-26T12:33:46.529740897Z
2023-09-26T12:33:46.529748371Z [Nest] 601 - 09/26/2023, 2:33:46 PM DEBUG [LoggingInterceptor] End /fleets(GET) from user 928bf625-ed3a-44c6-97a8-acf002c04dbe
With nestjs-paginate 8.3.1
[Nest] 826 - 09/26/2023, 2:35:50 PM DEBUG [LoggingInterceptor] Start /fleets(GET) from user 928bf625-ed3a-44c6-97a8-acf002c04dbe
2023-09-26T12:35:50.972951102Z query: SELECT DISTINCT "distinctAlias"."__root_id" AS "ids___root_id", "distinctAlias"."__root_id" FROM (SELECT "__root"."id" AS "__root_id", "__root"."name" AS "__root_name", "__root"."color" AS "__root_color", "__root"."caution" AS "__root_caution", "__root"."geoJson" AS "__root_geoJson", "__root"."stripeProductId" AS "__root_stripeProductId", "__root"."createdAt" AS "__root_createdAt", "__root"."updatedAt" AS "__root_updatedAt", "__root"."deletedAt" AS "__root_deletedAt", "__root"."operatorId" AS "__root_operatorId", "__root_vehicles_rel"."id" AS "__root_vehicles_rel_id", "__root_vehicles_rel"."serialNumber" AS "__root_vehicles_rel_serialNumber", "__root_vehicles_rel"."status" AS "__root_vehicles_rel_status", "__root_vehicles_rel"."deviceImei" AS "__root_vehicles_rel_deviceImei", "__root_vehicles_rel"."type" AS "__root_vehicles_rel_type", "__root_vehicles_rel"."isOutOfBound" AS "__root_vehicles_rel_isOutOfBound", "__root_vehicles_rel"."otherData" AS "__root_vehicles_rel_otherData", "__root_vehicles_rel"."createdAt" AS "__root_vehicles_rel_createdAt", "__root_vehicles_rel"."updatedAt" AS "__root_vehicles_rel_updatedAt", "__root_vehicles_rel"."deletedAt" AS "__root_vehicles_rel_deletedAt", "__root_vehicles_rel"."positionId" AS "__root_vehicles_rel_positionId", "__root_vehicles_rel"."currentTravelId" AS "__root_vehicles_rel_currentTravelId", "__root_vehicles_rel"."currentRentalId" AS "__root_vehicles_rel_currentRentalId", "__root_vehicles_rel"."operatorId" AS "__root_vehicles_rel_operatorId", "__root_vehicles_rel"."stationId" AS "__root_vehicles_rel_stationId", "__root_vehicles_rel"."agencyId" AS "__root_vehicles_rel_agencyId", "__root_vehicles_rel"."modelId" AS "__root_vehicles_rel_modelId", "__root_vehicles_rel"."fleetId" AS "__root_vehicles_rel_fleetId", "__root_vehicles_rel"."userId" AS "__root_vehicles_rel_userId", "__root_vehicles_rel_position_rel"."id" AS "__root_vehicles_rel_position_rel_id", "__root_vehicles_rel_position_rel"."latitude" AS "__root_vehicles_rel_position_rel_latitude", "__root_vehicles_rel_position_rel"."longitude" AS "__root_vehicles_rel_position_rel_longitude", "__root_vehicles_rel_position_rel"."createdAt" AS "__root_vehicles_rel_position_rel_createdAt", "__root_vehicles_rel_position_rel"."vehicleId" AS "__root_vehicles_rel_position_rel_vehicleId", "__root_vehicles_rel_position_rel"."deviceImei" AS "__root_vehicles_rel_position_rel_deviceImei", "__root_vehicles_rel_position_rel"."travelId" AS "__root_vehicles_rel_position_rel_travelId" FROM "fleet" "__root" LEFT JOIN "vehicle" "__root_vehicles_rel" ON "__root_vehicles_rel"."fleetId"="__root"."id" AND ("__root_vehicles_rel"."deletedAt" IS NULL) LEFT JOIN "position" "__root_vehicles_rel_position_rel" ON "__root_vehicles_rel_position_rel"."id"="__root_vehicles_rel"."positionId" WHERE ( (__root_operator_rel.id = $1) ) AND ( "__root"."deletedAt" IS NULL )) "distinctAlias" ORDER BY "distinctAlias"."__root_id" ASC, "__root_id" ASC LIMIT 20 -- PARAMETERS: ["9430f2fb-8225-444f-8b73-59b5de43ec68"]
2023-09-26T12:35:50.983962356Z query failed: SELECT DISTINCT "distinctAlias"."__root_id" AS "ids___root_id", "distinctAlias"."__root_id" FROM (SELECT "__root"."id" AS "__root_id", "__root"."name" AS "__root_name", "__root"."color" AS "__root_color", "__root"."caution" AS "__root_caution", "__root"."geoJson" AS "__root_geoJson", "__root"."stripeProductId" AS "__root_stripeProductId", "__root"."createdAt" AS "__root_createdAt", "__root"."updatedAt" AS "__root_updatedAt", "__root"."deletedAt" AS "__root_deletedAt", "__root"."operatorId" AS "__root_operatorId", "__root_vehicles_rel"."id" AS "__root_vehicles_rel_id", "__root_vehicles_rel"."serialNumber" AS "__root_vehicles_rel_serialNumber", "__root_vehicles_rel"."status" AS "__root_vehicles_rel_status", "__root_vehicles_rel"."deviceImei" AS "__root_vehicles_rel_deviceImei", "__root_vehicles_rel"."type" AS "__root_vehicles_rel_type", "__root_vehicles_rel"."isOutOfBound" AS "__root_vehicles_rel_isOutOfBound", "__root_vehicles_rel"."otherData" AS "__root_vehicles_rel_otherData", "__root_vehicles_rel"."createdAt" AS "__root_vehicles_rel_createdAt", "__root_vehicles_rel"."updatedAt" AS "__root_vehicles_rel_updatedAt", "__root_vehicles_rel"."deletedAt" AS "__root_vehicles_rel_deletedAt", "__root_vehicles_rel"."positionId" AS "__root_vehicles_rel_positionId", "__root_vehicles_rel"."currentTravelId" AS "__root_vehicles_rel_currentTravelId", "__root_vehicles_rel"."currentRentalId" AS "__root_vehicles_rel_currentRentalId", "__root_vehicles_rel"."operatorId" AS "__root_vehicles_rel_operatorId", "__root_vehicles_rel"."stationId" AS "__root_vehicles_rel_stationId", "__root_vehicles_rel"."agencyId" AS "__root_vehicles_rel_agencyId", "__root_vehicles_rel"."modelId" AS "__root_vehicles_rel_modelId", "__root_vehicles_rel"."fleetId" AS "__root_vehicles_rel_fleetId", "__root_vehicles_rel"."userId" AS "__root_vehicles_rel_userId", "__root_vehicles_rel_position_rel"."id" AS "__root_vehicles_rel_position_rel_id", "__root_vehicles_rel_position_rel"."latitude" AS "__root_vehicles_rel_position_rel_latitude", "__root_vehicles_rel_position_rel"."longitude" AS "__root_vehicles_rel_position_rel_longitude", "__root_vehicles_rel_position_rel"."createdAt" AS "__root_vehicles_rel_position_rel_createdAt", "__root_vehicles_rel_position_rel"."vehicleId" AS "__root_vehicles_rel_position_rel_vehicleId", "__root_vehicles_rel_position_rel"."deviceImei" AS "__root_vehicles_rel_position_rel_deviceImei", "__root_vehicles_rel_position_rel"."travelId" AS "__root_vehicles_rel_position_rel_travelId" FROM "fleet" "__root" LEFT JOIN "vehicle" "__root_vehicles_rel" ON "__root_vehicles_rel"."fleetId"="__root"."id" AND ("__root_vehicles_rel"."deletedAt" IS NULL) LEFT JOIN "position" "__root_vehicles_rel_position_rel" ON "__root_vehicles_rel_position_rel"."id"="__root_vehicles_rel"."positionId" WHERE ( (__root_operator_rel.id = $1) ) AND ( "__root"."deletedAt" IS NULL )) "distinctAlias" ORDER BY "distinctAlias"."__root_id" ASC, "__root_id" ASC LIMIT 20 -- PARAMETERS: ["9430f2fb-8225-444f-8b73-59b5de43ec68"]
2023-09-26T12:35:50.985860533Z error: error: missing FROM-clause entry for table "__root_operator_rel"
2023-09-26T12:35:51.014665254Z [Nest] 826 - 09/26/2023, 2:35:51 PM ERROR [ExceptionsHandler] missing FROM-clause entry for table "__root_operator_rel"
2023-09-26T12:35:51.014730579Z QueryFailedError: missing FROM-clause entry for table "__root_operator_rel"
2023-09-26T12:35:51.014739997Z at PostgresQueryRunner.query (/app/node_modules/typeorm/src/driver/postgres/PostgresQueryRunner.ts:299:19)
2023-09-26T12:35:51.014743574Z at processTicksAndRejections (node:internal/process/task_queues:95:5)
2023-09-26T12:35:51.014746560Z at async SelectQueryBuilder.loadRawResults (/app/node_modules/src/query-builder/SelectQueryBuilder.ts:3789:25)
2023-09-26T12:35:51.014749606Z at async SelectQueryBuilder.getRawMany (/app/node_modules/src/query-builder/SelectQueryBuilder.ts:1626:29)
2023-09-26T12:35:51.014752621Z at async SelectQueryBuilder.executeEntitiesAndRawResults (/app/node_modules/src/query-builder/SelectQueryBuilder.ts:3457:26)
2023-09-26T12:35:51.014755567Z at async SelectQueryBuilder.getManyAndCount (/app/node_modules/src/query-builder/SelectQueryBuilder.ts:1873:36)
2023-09-26T12:35:51.014758483Z at async paginate (/app/node_modules/nestjs-paginate/src/paginate.ts:306:32)
The query
Paginate config
In both case operatorId is not null and exist in database
Note: QueryBuilder works fine
vsamofal commented
I will take a look; looks like something with loadEagerRelations
if you can create a test case that fails it will help a lot, or at least share more details like entities
Alsdrouf commented
Fleet entity
@Entity()
export class Fleet implements IFleet {
@PrimaryGeneratedColumn('uuid')
readonly id: string;
@Column('varchar', { length: 255 })
name: string;
@Column('varchar', { length: 7 })
color: string;
@Column('int', { default: 0 })
caution: number;
@Column('jsonb', { nullable: true, default: null })
geoJson: any;
@ManyToOne(() => Operator, (operator) => operator.fleets)
readonly operator?: Operator
@CreateDateColumn()
readonly createdAt: Date;
@UpdateDateColumn()
readonly updatedAt: Date;
@DeleteDateColumn()
readonly deletedAt: Date | null;
}
Operator entity
@Entity()
export class Operator {
@PrimaryGeneratedColumn('uuid')
readonly id: string;
@Column('varchar', { length: 128, unique: true })
name: string;
@Column('varchar', { length: 128 })
logoUrl: string;
@Column('jsonb')
theme: any;
@Column('varchar', {
length: 1024,
nullable: true,
transformer: {
from: (value: string) => (value && value.length > 0 ? value.split(',') : null),
to: (value: string[]) => (value && value.length > 0 ? value.join(',') : null),
},
})
mailingList: string[];
@OneToMany(() => Fleet, (fleet) => fleet.operator)
readonly fleets?: Fleet[];
@CreateDateColumn()
readonly createdAt: Date;
@UpdateDateColumn()
readonly updatedAt: Date;
@DeleteDateColumn()
readonly deletedAt: Date | null;
}
It's really simplified entity, note that I am using postgres
I am not using any eager relation in all my entity
Alsdrouf commented
I will try to write a test
Alsdrouf commented
it('should work', async () => {
const config: PaginateConfig<CatToyEntity> = {
sortableColumns: ['id'],
where: {cat: {name: cats[0].name}}
}
const query: PaginateQuery = {
path: '',
}
const result = await paginate<CatToyEntity>(query, catToyRepo, config)
expect(result.data.length).toStrictEqual(3)
})
Found it
Error is when where contain a not loaded relations
Exemple that really work
it('should work', async () => {
const config: PaginateConfig<CatToyEntity> = {
relations: ['cat']
sortableColumns: ['id'],
where: {cat: {name: cats[0].name}}
}
const query: PaginateQuery = {
path: '',
}
const result = await paginate<CatToyEntity>(query, catToyRepo, config)
expect(result.data.length).toStrictEqual(3)
})