Limit/offset not working in combination with joins with Postgres DB
jonasnilsberg opened this issue ยท 21 comments
I'm getting error QueryFailedError: column reference "Ticket_id" is ambiguous
when trying to query findMany with both a join and a specified limit when using Postgres DB. Tried the same code, but with a SQLite DB and it works as intended.
Any suggestions on how to get this to work with Postgres?
Error
QueryFailedError: column reference "Ticket_id" is ambiguous
at PostgresQueryRunner.query (/Users/jonasnr/WebstormProjects/nest_test/src/driver/postgres/PostgresQueryRunner.ts:299:19)
at processTicksAndRejections (internal/process/task_queues.js:95:5)
at SelectQueryBuilder.loadRawResults (/Users/jonasnr/WebstormProjects/nest_test/src/query-builder/SelectQueryBuilder.ts:3519:25)
at SelectQueryBuilder.getRawMany (/Users/jonasnr/WebstormProjects/nest_test/src/query-builder/SelectQueryBuilder.ts:1551:29)
at SelectQueryBuilder.executeEntitiesAndRawResults (/Users/jonasnr/WebstormProjects/nest_test/src/query-builder/SelectQueryBuilder.ts:3213:26)
at SelectQueryBuilder.getRawAndEntities (/Users/jonasnr/WebstormProjects/nest_test/src/query-builder/SelectQueryBuilder.ts:1595:29)
at SelectQueryBuilder.getMany (/Users/jonasnr/WebstormProjects/nest_test/src/query-builder/SelectQueryBuilder.ts:1685:25)
Query
'SELECT DISTINCT distinctAlias.Ticket_id as "ids_Ticket_id" FROM (SELECT Ticket.id AS Ticket_id, Ticket.id AS Ticket_id, Ticket.ticketName AS Ticket_ticketName, user.id AS user_id, user.id AS user_id, user.owner AS user_owner, user.username AS user_username, user.scope AS user_scope, user.ticketId AS user_ticketId FROM ticket Ticket LEFT JOIN user user ON user.ticketId=Ticket.id) distinctAlias ORDER BY Ticket_id ASC LIMIT 1'
See all files below:
Service
@Injectable()
export class TicketService extends TypeOrmCrudService<Ticket> {
constructor(@InjectRepository(Ticket) repo) {
super(repo);
}
}
Controller
@ApiTags('tickets')
@Crud({
model: { type: Ticket },
query: {
join: {
user: { eager: true },
},
limit: 1,
},
})
@Controller('ticket')
export class TicketController implements CrudController<Ticket> {
constructor(public service: TicketService) {}
}
Entities
@Entity('ticket')
export class Ticket {
@PrimaryGeneratedColumn()
id: number;
@Column()
ticketName: string;
@OneToOne(() => User, (user) => user.ticket)
user: User;
}
@Entity('user')
export class User {
@PrimaryGeneratedColumn()
id: number;
@Column()
owner: string;
@Column()
username: string;
@Column()
scope: string;
@OneToOne(() => Ticket)
@JoinColumn()
ticket: Ticket;
}
Module
@Module({
imports: [
TypeOrmModule.forRoot({
type: 'postgres',
host: 'localhost',
port: 5432,
username: 'postgres',
password: 'postgres',
database: 'postgres',
autoLoadEntities: true,
synchronize: true,
}),
TicketModule,
],
controllers: [AppController],
providers: [AppService],
})
export class AppModule {
constructor() {}
}
+1
P/S: When I downgrade typeorm
to 0.2.45
the problem has solved
Same issue
After downgrading to typeorm@0.2.45
had these errors:
node_modules/@nestjsx/crud-typeorm/lib/typeorm-crud.service.d.ts:30:5 - error TS2416: Property 'createOne' in type 'TypeOrmCrudService<T>' is not assignable to the same property in base type 'CrudService<T>'. Type '(req: CrudRequest, dto: DeepPartial<T>) => Promise<T>' is not assignable to type '(req: CrudRequest, dto: T) => Promise<T>'. Types of parameters 'dto' and 'dto' are incompatible.
Type 'T' is not assignable to type 'DeepPartial<T>'.
30 createOne(req: CrudRequest, dto: DeepPartial<T>): Promise<T>;
~~~~~~~~~
node_modules/@nestjsx/crud-typeorm/lib/typeorm-crud.service.d.ts:32:5 - error TS2416: Property 'updateOne' in type 'TypeOrmCrudService<T>' is not assignable to the same property in base type 'CrudService<T>'.
Type '(req: CrudRequest, dto: DeepPartial<T>) => Promise<T>' is not assignable to type '(req: CrudRequest, dto: T) => Promise<T>'.
Types of parameters 'dto' and 'dto' are incompatible.
Type 'T' is not assignable to type 'DeepPartial<T>'.
32 updateOne(req: CrudRequest, dto: DeepPartial<T>): Promise<T>;
~~~~~~~~~
node_modules/@nestjsx/crud-typeorm/lib/typeorm-crud.service.d.ts:33:5 - error TS2416: Property 'replaceOne' in type 'TypeOrmCrudService<T>' is not assignable to the same property in base type 'CrudService<T>'.
Type '(req: CrudRequest, dto: DeepPartial<T>) => Promise<T>' is not assignable to type '(req: CrudRequest, dto: T) => Promise<T>'.
Types of parameters 'dto' and 'dto' are incompatible.
Type 'T' is not assignable to type 'DeepPartial<T>'.
33 replaceOne(req: CrudRequest, dto: DeepPartial<T>): Promise<T>;
But 0.2.41
has worked for me
Same
Same issue here
Same
Same !!
+1
P/S: When I downgrade
typeorm
to0.2.45
the problem has solved
This is not a solution ! Is there an open issue for the upgrade typeorm ?
Same here, the pagination is not working bc i cant set the limit
--Edit
Seems like the primary columns is being set twice, I commented the line in crud-typeorm/src/typeorm-crud.service.ts#789 and its now working, but idk the side effects of doing that yet.
https://github.com/Coystark/crud-typeorm/blob/main/src/typeorm-crud.service.ts#L789
Same here, the pagination is not working bc i cant set the limit
--Edit
Seems like the primary columns is being set twice, I commented the line in crud-typeorm/src/typeorm-crud.service.ts#789 and its now working, but idk the side effects of doing that yet.
https://github.com/Coystark/crud-typeorm/blob/main/src/typeorm-crud.service.ts#L789
How did you do that ? i only have service.d.ts in my lib folder
Has anyone been able to use this with typeorm 0.3.7? If so, how?
Crud rewiko
Maybe this patch helps someone:
import { uniq } from 'lodash'
@Injectable()
export class MyEntityService extends TypeOrmCrudService<MyEntity> {
getSelect(query, options) {
return uniq(super.getSelect(query, options))
}
}
I dont know yet, but its works fine! Has some version fixed it?
Faced same issue
@5andi5 Nice solution! It helped to solve an issue with the first-level fields, but now duplication appears on the referenced table id (in my case it's MySql) Any idea what to override to solve second-level id duplication problems?
@5andi5 >
Thanks! It works, but only for the parent entity. This error appears for id column in the nested object. And unfortunately, this trick is not working even if I override "getSelect" for the nested object service.
I actually just found sth that worked, add exclude: ['whateveryourprimaryidis'] in to query section then boom it works
There is a better npm for CURD generator with all query params for filter supported, this might help someone,
https://www.npmjs.com/package/@prabink/nestjsx-typeorm-curd
@fatats96 Your solutions works. I've been on this issue for over 2 weeks and I couldn't anything useful.
I'm not sure how or why it works though
Hi guys,! I faced with the same problem. Why don't you fix this bug in the current repository and instead forking it?