node.js - TypeORM 选择所有行但限制 25

Question

CandidateEntity

@Entity({ name: 'users' })
export class CandidateEntity {
    @PrimaryGeneratedColumn()
    public id: number;

    @OneToOne(() => CandidateEmployeeInfoEntity, employeeInfo => employeeInfo.candidate)
    public employeeInfo: CandidateEmployeeInfoEntity;
}

EmployeeInfoEntity

@Entity({ name: 'candidates_employee_infos' })
export class CandidateEmployeeInfoEntity {
    @PrimaryGeneratedColumn()
    public id: number;

    @Column({ type: 'bool', nullable: false })
    public relocation: boolean;

    @Column({ type: 'text', nullable: true })
    public softSkills: string;

    @OneToOne(() => CandidateEntity, candidate => candidate.employeeInfo)
    public candidate: CandidateEntity;

    @Column({ type: 'integer' })
    public candidateId: number;
}

我创建查询以从数据库中的 104 行中选择前 25 行

const {
    perPage = 25,
    page = 1,
} = params;
const skip = (perPage * page) - perPage;

let candidatesQuery = this.candidateRepository.createQueryBuilder('candidates');
candidatesQuery = candidatesQuery.leftJoinAndSelect(`candidates.employeeInfo`, 'employeeInfo'); // problem in this relation
candidatesQuery = candidatesQuery.skip(skip);
candidatesQuery = candidatesQuery.take(perPage);

const { entities, raw } = await candidatesQuery.getRawAndEntities();
const count = await candidatesQuery.getCount();

console.log(entities.length) // 104 rows
console.log(raw.length) // 104 rows
console.log(count) // 104 rows

当typeorm返回不正确结果时输出sql查询

第一个查询

SELECT DISTINCT "distinctAlias"."candidates_id" as "ids_candidates_id" FROM (SELECT "candidates"."id" AS "candidates_id", "candidates"."uuid" AS "candidates_uuid", "candidates"."role" AS "candidates_role", "candidates"."first_name" AS "candidates_first_name", "candidates"."last_name" AS "candidates_last_name", "candidates"."email" AS "candidates_email", "candidates"."phone" AS "candidates_phone", "candidates"."phone_prefix" AS "candidates_phone_prefix", "candidates"."country_id" AS "candidates_country_id", "candidates"."city_id" AS "candidates_city_id", "candidates"."avatar" AS "candidates_avatar", "candidates"."confirmed_at" AS "candidates_confirmed_at", "candidates"."is_generated" AS "candidates_is_generated", "candidates"."created_at" AS "candidates_created_at", "candidates"."birthday" AS "candidates_birthday", "candidates"."type" AS "candidates_type", "employeeInfo"."id" AS "employeeInfo_id", "employeeInfo"."hourly_rate_from" AS "employeeInfo_hourly_rate_from", "employeeInfo"."hourly_rate_to" AS "employeeInfo_hourly_rate_to", "employeeInfo"."hourly_rate_currency" AS "employeeInfo_hourly_rate_currency", "employeeInfo"."salary_rate_from" AS "employeeInfo_salary_rate_from", "employeeInfo"."salary_rate_to" AS "employeeInfo_salary_rate_to", "employeeInfo"."salary_rate_currency" AS "employeeInfo_salary_rate_currency", "employeeInfo"."relocation" AS "employeeInfo_relocation", "employeeInfo"."soft_skills" AS "employeeInfo_soft_skills", "employeeInfo"."candidate_id" AS "employeeInfo_candidate_id" FROM "users" "candidates" LEFT JOIN "candidates_employee_infos" "employeeInfo" ON "employeeInfo"."candidate_id"="candidates"."id" WHERE  "candidates"."type" IN ($1)) "distinctAlias" ORDER BY "candidates_id" ASC LIMIT 25

第二个查询

SELECT "candidates"."id" AS "candidates_id", "candidates"."uuid" AS "candidates_uuid", "candidates"."role" AS "candidates_role", "candidates"."first_name" AS "candidates_first_name", "candidates"."last_name" AS "candidates_last_name", "candidates"."email" AS "candidates_email", "candidates"."phone" AS "candidates_phone", "candidates"."phone_prefix" AS "candidates_phone_prefix", "candidates"."country_id" AS "candidates_country_id", "candidates"."city_id" AS "candidates_city_id", "candidates"."avatar" AS "candidates_avatar", "candidates"."confirmed_at" AS "candidates_confirmed_at", "candidates"."is_generated" AS "candidates_is_generated", "candidates"."created_at" AS "candidates_created_at", "candidates"."birthday" AS "candidates_birthday", "candidates"."type" AS "candidates_type", "employeeInfo"."id" AS "employeeInfo_id", "employeeInfo"."hourly_rate_from" AS "employeeInfo_hourly_rate_from", "employeeInfo"."hourly_rate_to" AS "employeeInfo_hourly_rate_to", "employeeInfo"."hourly_rate_currency" AS "employeeInfo_hourly_rate_currency", "employeeInfo"."salary_rate_from" AS "employeeInfo_salary_rate_from", "employeeInfo"."salary_rate_to" AS "employeeInfo_salary_rate_to", "employeeInfo"."salary_rate_currency" AS "employeeInfo_salary_rate_currency", "employeeInfo"."relocation" AS "employeeInfo_relocation", "employeeInfo"."soft_skills" AS "employeeInfo_soft_skills", "employeeInfo"."candidate_id" AS "employeeInfo_candidate_id" FROM "users" "candidates" LEFT JOIN "candidates_employee_infos" "employeeInfo" ON "employeeInfo"."candidate_id"="candidates"."id" WHERE  "candidates"."type" IN ($1)

第三个查询

SELECT COUNT(DISTINCT("candidates"."id")) as "cnt" FROM "users" "candidates" LEFT JOIN "candidates_employee_infos" "employeeInfo" ON "employeeInfo"."candidate_id"="candidates"."id" WHERE  "candidates"."type" IN ($1)

当我删除负载关系employeeInfo时。
这条线candidatesQuery = CandidatesQuery.leftJoinAndSelect('candidates.employeeInfo', 'employeeInfo');

TypeORM 返回 25 行

console.log(entities.length) // 25 rows
console.log(raw.length) // 25 rows
console.log(count) // 104 rows

为什么？以及如何解决这个问题？

Answer 1

当您使用Leftjoin时，typeorm不会将LIMIT添加到其查询中。

来自 TypeORM 的创建者:

take and skip functionality does not work with raw data since its internal ORM functionality https://github.com/typeorm/typeorm/issues/1768

因此，要解决此问题，有两种选择:

1. 如果您希望 typeORM 为您做这件事，您应该使用 getMany，而不是 raw

2. 如果您想要原始结果，您应该使用 .offset 和 .limit 而不是 .skip、.take。因为 .offset 和 .limit 会改变 SQL 查询。

注意:在实现分页时最好按某些内容排序