sql - 如何在 TypeORM 查询生成器中选择特定列（多对多关系）

Question

我一直在寻找信息和解决方案，但找不到任何与我的问题相符的东西。最接近的是这个问题 [ https://stackoverflow.com/questions/64401212/how-to-select-specific-columns-in-typeorm-querybuilder?newreg=88f0c8cadd924456a61eddb8a5611421 ] 我把它当作一个起点。

我使用 PostgreSQL，我的实体是：

用户实体：

@Entity('user')
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @CreateDateColumn({
    type: 'timestamp',
    default: () => 'CURRENT_TIMESTAMP(6)',
  })
  createdAt: Date;

  @CreateDateColumn({
    type: 'timestamp',
    default: () => 'CURRENT_TIMESTAMP(6)',
    onUpdate: 'CURRENT_TIMESTAMP(6)',
  })
  updatedAt: Date;

  @ApiProperty()
  @Column({ type: 'varchar', default: null })
  name: string;

  ..... more columns .......

  @ManyToMany(() => Calendar)
  @JoinTable({
    name: 'users_calendars',
    joinColumn: {
      name: 'idUser',
      referencedColumnName: 'id',
    },
    inverseJoinColumn: {
      name: 'idCalendar',
      referencedColumnName: 'idCalendar',
    },
  })
  calendars: Calendar[];
}

日历实体：

@Entity('calendar')
export class Calendar {
  @PrimaryGeneratedColumn()
  idCalendar: number;

  @CreateDateColumn({
    type: 'timestamp',
    default: () => 'CURRENT_TIMESTAMP(6)',
  })
  createdAt: Date;

  @CreateDateColumn({
    type: 'timestamp',
    default: () => 'CURRENT_TIMESTAMP(6)',
    onUpdate: 'CURRENT_TIMESTAMP(6)',
  })
  updatedAt: Date;

  @Column('varchar')
  operation: string;

  @Column('varchar', { nullable: true })
  url?: string;

  @Column('varchar', { nullable: true })
  path?: string;
}

因此，考虑到这一点，我想创建一个查询，将所有相关日历发送给某个用户。

我一直在尝试一些原始 SQL 查询，我想要的是这样的：

SELECT 
    "calendar"."idCalendar",  
    "calendar"."operation",  
    "calendar"."url",  
    "calendar"."path" 
FROM 
    "user" "user" 
LEFT JOIN 
    "users_calendars" "user_calendar" ON "user_calendar"."idUser"="user"."id"
LEFT JOIN 
    "calendar" "calendar" ON "calendar"."idCalendar"="user_calendar"."idCalendar" 
WHERE 
    "user"."id" = 1 and (
        "calendar"."operation" like '%1636454981858%' or 
        "calendar"."operation" like '%IVA%' or
        "calendar"."operation" like '%condition 3%'
    )

这个查询有效！！

我正在使用 nestjs 和 typeorm 使用这个 queryBuilder 方法，如下所示：

let queryCalendars = `"calendar"."operation" like '%IVA%' or "calendar"."operation" like '%1636454785616-914385082%' or "calendar"."operation" like '%IVA 3%' or "calendar"."operation" like '%IVA 4%'`;

const user: User = await this.connection
      .getRepository(User)
      .createQueryBuilder('user')
      .leftJoinAndSelect('user.calendars', 'calendar')
      .select([
        'calendar.idCalendar',
        'calendar.operation',
        'calendar.url',
        'calendar.path',
      ])
      .where(`user.id = :idUser and (${queryCalendars})`, { idUser })
      .getOne();

并创建此查询：

SELECT 
    "calendar"."idCalendar" AS "calendar_idCalendar", 
    "calendar"."operation" AS "calendar_operation", 
    "calendar"."url" AS "calendar_url", 
    "calendar"."path" AS "calendar_path" 
FROM 
    "user" "user" 
LEFT JOIN 
    "users_calendars" "user_calendar" ON "user_calendar"."idUser"="user"."id" 
LEFT JOIN 
    "calendar" "calendar" ON "calendar"."idCalendar"="user_calendar"."idCalendar" 
WHERE 
    "user"."id" = :idUser and (
        "calendar"."operation" like '%IVA%' or 
        "calendar"."operation" like '%1636454785616-914385082%' or 
        "calendar"."operation" like '%IVA 3%' or 
        "calendar"."operation" like '%IVA 4%'
    )

此查询不起作用！！！！

我正在失去理智，因为我无法找出这些查询之间的区别以及为什么我可以让它工作......

我期望得到的是：

[
 { 
   idCalendar: 1, 
   operation: "operation-name", 
   url: "https://some.url", 
   path: "./some/path" 
 },
 ...
]

但相反，我得到了未定义的......

标签： sqlpostgresqlnestjstypeormnode.js-typeorm