TypeORM条件查询结果不匹配：where参数未在SQL中生效

我在TypeORM的findOne语句里设置了where: {session: 1, user: 2}，但查询结果完全不符合预期，而且生成的SQL里根本找不到这两个参数的过滤条件。相关代码和生成的SQL如下：

查询代码 #

async getRegister() {
  const result = await this.registerModel.findOne({
    where: {
      session: 1,
      user: 2,
    },
    select: ['id', 'created_time'],
  });

  return result;
}

Register实体定义 #

@Entity('register')
export class Register {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({
    type: 'text',
    nullable: true,
    unique: true,
  })
  no: string;
  ……
  @ManyToOne(type => User, user => user.registers)
  user: User;

  @OneToOne(type => Session)
  @JoinColumn()
  session: Session;
}

生成的SQL #

query: SELECT DISTINCT "distinctAlias"."Register_id" AS "ids_Register_id" FROM (SELECT "Register"."id" AS "Register_id", "Register"."created_time" AS "Register_created_time" FROM "register" "Register" INNER JOIN "session" "Register__Register_session" ON "Register__Register_session"."id"="Register"."sessionId" AND ("Register__Register_session"."deleted_time" IS NULL)  INNER JOIN "user" "Register__Register_user" ON "Register__Register_user"."id"="Register"."userId" AND ("Register__Register_user"."deleted_time" IS NULL) WHERE "Register"."deleted_time" IS NULL) "distinctAlias" ORDER BY "Register_id" ASC LIMIT 1
query: SELECT "Register"."id" AS "Register_id", "Register"."created_time" AS "Register_created_time" FROM "register" "Register" INNER JOIN "session" "Register__Register_session" ON "Register__Register_session"."id"="Register"."sessionId" AND ("Register__Register_session"."deleted_time" IS NULL)  INNER JOIN "user" "Register__Register_user" ON "Register__Register_user"."id"="Register"."userId" AND ("Register__Register_user"."deleted_time" IS NULL) WHERE ( "Register"."deleted_time" IS NULL ) AND ( "Register"."id" IN (7) )

问题原因 #

session和user是实体的关联属性，直接传入数字ID不符合TypeORM的查询规则——TypeORM无法将数字直接映射到关联实体的过滤条件，因此不会生成对应的WHERE子句。

解决方案 #

方案1：直接使用外键列名查询

实体中session对应的外键列是sessionId，user对应的是userId，直接用这些列名写条件即可：

async getRegister() {
  const result = await this.registerModel.findOne({
    where: {
      sessionId: 1,
      userId: 2,
    },
    select: ['id', 'created_time'],
  });

  return result;
}

方案2：使用关联实体的主键嵌套查询

如果要保持关联属性的写法，需要通过嵌套对象指定关联实体的主键字段：

async getRegister() {
  const result = await this.registerModel.findOne({
    where: {
      session: { id: 1 },
      user: { id: 2 },
    },
    select: ['id', 'created_time'],
  });

  return result;
}

如果需要更严格的TypeScript类型检查，可以使用FindOptionsWhere显式定义条件：

import { FindOptionsWhere } from 'typeorm';

async getRegister() {
  const whereCondition: FindOptionsWhere<Register> = {
    session: { id: 1 },
    user: { id: 2 },
  };
  
  const result = await this.registerModel.findOne({
    where: whereCondition,
    select: ['id', 'created_time'],
  });

  return result;
}

内容的提问来源于stack exchange，提问作者PeachGas