TypeORM select all rows but limit 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;
}

I create query to select first 25 rows from 104 rows in database

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

Output sql query when typeorm return not correct results

first query

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

second query

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)

third query

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)

When I remove load relation employeeInfo.
This line candidatesQuery = candidatesQuery.leftJoinAndSelect('candidates.employeeInfo', 'employeeInfo');

TypeORM return 25 rows

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

Why ? And how to fix this problem ?

Answer 1

When you use Leftjoin typeorm doesn't add the LIMIT to its query.

From the creator of TypeORM:

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

So to fix it there are two options:

1. If you want typeORM to do it for you, you should use getMany, instead of raw

2. If you want raw results you should can use .offset and .limit instead of .skip, .take. Since .offset and .limit will change the SQL query.

Note: it is a good idea to order by something when you are implementing pagination

Answer 2

You can use take and skip with repository pattern:

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

const candidates = await this.candidateRepository.find({
    relations: ['employeeInfo'],
    take: perPage,
    skip,
});

also for pagination purpose I recommend to use findAndCount

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

const [data, total] = await this.repo.findAndCount({
    take,
    skip,
    // ...other options
});