Limit/offset not working in combination with joins with Postgres DB

[jonasnilsberg]

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() {}
}

[hoangnq3004]

+1

P/S: When I downgrade typeorm to 0.2.45 the problem has solved

[hihhhello]

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

[professrr]

Same

[comonadd]

Same issue here

[jongomes]

Same

[ghost]

Same !!

[ghost]

+1

P/S: When I downgrade typeorm to 0.2.45 the problem has solved

This is not a solution ! Is there an open issue for the upgrade typeorm ?

[Coystark]

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

[fleetimee]

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

[nicolasLuduena]

Has anyone been able to use this with typeorm 0.3.7? If so, how?

[jongomes]

Crud rewiko

[5andi5]

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))
  }
}

[luizgomesdev]

I dont know yet, but its works fine! Has some version fixed it?

[jongomes]

@luizgomesdev https://github.com/rewiko/crud

[valdeua]

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?

[voznyy]

@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.

[fatats96]

I actually just found sth that worked, add exclude: ['whateveryourprimaryidis'] in to query section then boom it works

[prabinkarki643]

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

[Lesleytech]

@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

[umar-bunu]

@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 Not me seeing this months after you...

[idchrono]

Faced the same issue, still not fixed (

[Lesleytech]

@idchrono The following fixes it for me. It's weird but it works

@Crud({
   model: .....,
   query: {
      exclude: ['id'] // This line fixes QueryFailedError: column reference "Some_id" is ambiguous
   }
})

This solution is provided above by @fatats96