Typeorm error in pagination with order by clause

Issue description

I have a entity called FinPay and it has a one to many relation with FinInstallmentsPay. I'm trying to paginate and order by FinInstallmentsPay.dt_due, but it's not getting the correct number of rows, when I take 10 and skip 0 I get 7 FinPay items, because when typeorm take the FinPay ids to put in the where clause ("FinPay"."Id" IN ()) it's duplicating the ids.

Expected Behavior

To return 10 FinPay items.

Actual Behavior

query: SELECT DISTINCT "distinctAlias"."FinPay_id" AS "ids_FinPay_id", "distinctAlias"."FinPay__FinPay_installments_dt_due" FROM (SELECT "FinPay"."id" AS "FinPay_id", "FinPay"."id_client" AS "FinPay_id_client", "FinPay"."id_form " AS "FinPay_id_form", "FinPay"."total" AS "FinPay_total", "FinPay"."description" AS "FinPay_description", "FinPay"."down_payment" AS "FinPay_down_payment", "FinPay"."id_account" AS "FinPay_id_account", "FinPay"."id_account_plan " AS "FinPay_id_account_plan", "FinPay"."payment_type" AS "FinPay_payment_type", "FinPay__FinPay_installments"."id" AS "FinPay__FinPay_installments_id", "FinPay__FinPay_installments"."id_pay" AS "FinPay__FinPay_installments_id_p ay", "FinPay__FinPay_installments"."dt_due" AS "FinPay__FinPay_installments_dt_due", "FinPay__FinPay_installments"."dt_payment" AS "FinPay__FinPay_installments_dt_payment", "FinPay__FinPay_installments"."value" AS "FinPay__FinPa y_installments_value", "FinPay__FinPay_installments"."installment" AS "FinPay__FinPay_installments_installment", "FinPay__FinPay_installments"."installment_quantity" AS "FinPay__FinPay_installments_installment_quantity", "FinPay __client"."id" AS "FinPay__client_id", "FinPay__client"."document" AS "FinPay__client_document", "FinPay__client"."telephone" AS "FinPay__client_telephone", "FinPay__client"."dt_birth" AS "FinPay__client_dt_birth", "FinPay__clie nt"."fantasy_name" AS "FinPay__client_fantasy_name", "FinPay__client"."person_type" AS "FinPay__client_person_type", "FinPay__client"."name" AS "FinPay__client_name", "FinPay__client"."active" AS "FinPay__client_active", "FinPay __client__addresses"."id" AS "FinPay__client__addresses_id", "FinPay__client__addresses"."city" AS "FinPay__client__addresses_city", "FinPay__client__addresses"."district" AS "FinPay__client__addresses_district", "FinPay__client __addresses"."id_person" AS "FinPay__client__addresses_id_person", "FinPay__client__addresses"."complement" AS "FinPay__client__addresses_complement", "FinPay__client__addresses"."state" AS "FinPay__client__addresses_state", "Fi nPay__client__addresses"."street" AS "FinPay__client__addresses_street", "FinPay__client__addresses"."zip_code" AS "FinPay__client__addresses_zip_code", "FinPay__client__addresses"."number" AS "FinPay__client__addresses_number", "FinPay__client__emails"."id" AS "FinPay__client__emails_id", "FinPay__client__emails"."email" AS "FinPay__client__emails_email", "FinPay__client__emails"."id_person" AS "FinPay__client__emails_id_person", "FinPay__form"."id" A S "FinPay__form_id", "FinPay__form"."forms" AS "FinPay__form_forms", "FinPay__items"."id" AS "FinPay__items_id", "FinPay__items"."id_pay" AS "FinPay__items_id_pay", "FinPay__items"."description" AS "FinPay__items_description", " FinPay__items"."quantity" AS "FinPay__items_quantity", "FinPay__items"."value" AS "FinPay__items_value", "FinPay__items"."id_product" AS "FinPay__items_id_product", "FinPay__account"."id" AS "FinPay__account_id", "FinPay__accoun t"."account" AS "FinPay__account_account", "FinPay__account"."active" AS "FinPay__account_active", "FinPay__account_plan"."id" AS "FinPay__account_plan_id", "FinPay__account_plan"."account_plan" AS "FinPay__account_plan_account_ plan", "FinPay__account_plan"."active" AS "FinPay__account_plan_active", "FinPay__account_plan"."type" AS "FinPay__account_plan_type" FROM "fin_pay" "FinPay" LEFT JOIN "fin_installments_pay" "FinPay__FinPay_installments" ON "Fin Pay__FinPay_installments"."id_pay"="FinPay"."id" LEFT JOIN "people" "FinPay__client" ON "FinPay__client"."id"="FinPay"."id_client" LEFT JOIN "addresses" "FinPay__client__addresses" ON "FinPay__client__addresses"."id_person"="F inPay__client"."id" LEFT JOIN "emails" "FinPay__client__emails" ON "FinPay__client__emails"."id_person"="FinPay__client"."id" LEFT JOIN "fin_forms" "FinPay__form" ON "FinPay__form"."id"="FinPay"."id_form" LEFT JOIN "fin_pay_i tems" "FinPay__items" ON "FinPay__items"."id_pay"="FinPay"."id" LEFT JOIN "fin_accounts" "FinPay__account" ON "FinPay__account"."id"="FinPay"."id_account" LEFT JOIN "fin_account_plans" "FinPay__account_plan" ON "FinPay__account_plan"."id"="FinPay"."id_account_plan" WHERE (NOT("FinPay"."id" IS NULL))) "distinctAlias" ORDER BY "distinctAlias"."FinPay__FinPay_installments_dt_due" DESC, "FinPay_id" ASC LIMIT 10 query: SELECT "FinPay"."id" AS "FinPay_id", "FinPay"."id_client" AS "FinPay_id_client", "FinPay"."id_form" AS "FinPay_id_form", "FinPay"."total" AS "FinPay_total", "FinPay"."description" AS "FinPay_description", "FinPay"."down_p ayment" AS "FinPay_down_payment", "FinPay"."id_account" AS "FinPay_id_account", "FinPay"."id_account_plan" AS "FinPay_id_account_plan", "FinPay"."payment_type" AS "FinPay_payment_type", "FinPay__FinPay_installments"."id" AS "Fin Pay__FinPay_installments_id", "FinPay__FinPay_installments"."id_pay" AS "FinPay__FinPay_installments_id_pay", "FinPay__FinPay_installments"."dt_due" AS "FinPay__FinPay_installments_dt_due", "FinPay__FinPay_installments"."dt_paym ent" AS "FinPay__FinPay_installments_dt_payment", "FinPay__FinPay_installments"."value" AS "FinPay__FinPay_installments_value", "FinPay__FinPay_installments"."installment" AS "FinPay__FinPay_installments_installment", "FinPay__F inPay_installments"."installment_quantity" AS "FinPay__FinPay_installments_installment_quantity", "FinPay__client"."id" AS "FinPay__client_id", "FinPay__client"."document" AS "FinPay__client_document", "FinPay__client"."telephon e" AS "FinPay__client_telephone", "FinPay__client"."dt_birth" AS "FinPay__client_dt_birth", "FinPay__client"."fantasy_name" AS "FinPay__client_fantasy_name", "FinPay__client"."person_type" AS "FinPay__client_person_type", "FinPa y__client"."name" AS "FinPay__client_name", "FinPay__client"."active" AS "FinPay__client_active", "FinPay__client__addresses"."id" AS "FinPay__client__addresses_id", "FinPay__client__addresses"."city" AS "FinPay__client__address es_city", "FinPay__client__addresses"."district" AS "FinPay__client__addresses_district", "FinPay__client__addresses"."id_person" AS "FinPay__client__addresses_id_person", "FinPay__client__addresses"."complement" AS "FinPay__cli ent__addresses_complement", "FinPay__client__addresses"."state" AS "FinPay__client__addresses_state", "FinPay__client__addresses"."street" AS "FinPay__client__addresses_street", "FinPay__client__addresses"."zip_code" AS "FinPay_ _client__addresses_zip_code", "FinPay__client__addresses"."number" AS "FinPay__client__addresses_number", "FinPay__client__emails"."id" AS "FinPay__client__emails_id", "FinPay__client__emails"."email" AS "FinPay__client__emails_ email", "FinPay__client__emails"."id_person" AS "FinPay__client__emails_id_person", "FinPay__form"."id" AS "FinPay__form_id", "FinPay__form"."forms" AS "FinPay__form_forms", "FinPay__items"."id" AS "FinPay__items_id", "FinPay__i tems"."id_pay" AS "FinPay__items_id_pay", "FinPay__items"."description" AS "FinPay__items_description", "FinPay__items"."quantity" AS "FinPay__items_quantity", "FinPay__items"."value" AS "FinPay__items_value", "FinPay__items"."i d_product" AS "FinPay__items_id_product", "FinPay__account"."id" AS "FinPay__account_id", "FinPay__account"."account" AS "FinPay__account_account", "FinPay__account"."active" AS "FinPay__account_active", "FinPay__account_plan"." id" AS "FinPay__account_plan_id", "FinPay__account_plan"."account_plan" AS "FinPay__account_plan_account_plan", "FinPay__account_plan"."active" AS "FinPay__account_plan_active", "FinPay__account_plan"."type" AS "FinPay__account_ plan_type" FROM "fin_pay" "FinPay" LEFT JOIN "fin_installments_pay" "FinPay__FinPay_installments" ON "FinPay__FinPay_installments"."id_pay"="FinPay"."id" LEFT JOIN "people" "FinPay__client" ON "FinPay__client"."id"="FinPay"."id _client" LEFT JOIN "addresses" "FinPay__client__addresses" ON "FinPay__client__addresses"."id_person"="FinPay__client"."id" LEFT JOIN "emails" "FinPay__client__emails" ON "FinPay__client__emails"."id_person"="FinPay__client"." id" LEFT JOIN "fin_forms" "FinPay__form" ON "FinPay__form"."id"="FinPay"."id_form" LEFT JOIN "fin_pay_items" "FinPay__items" ON "FinPay__items"."id_pay"="FinPay"."id" LEFT JOIN "fin_accounts" "FinPay__account" ON "FinPay__acc ount"."id"="FinPay"."id_account" LEFT JOIN "fin_account_plans" "FinPay__account_plan" ON "FinPay__account_plan"."id"="FinPay"."id_account_plan" WHERE ( (NOT("FinPay"."id" IS NULL)) ) AND ( "FinPay"."id" IN (33, 21, 32, 30, 33, 8, 10, 25, 21, 32) ) ORDER BY "FinPay__FinPay_installments"."dt_due" DESC <<-- here i have the problem duplicating ids, Here is my repository to get all:

async GetAll(
    page: number,
    recordsPerPage: number,
    id_company?: number,
    filter?: FilterObject[],
    order?: OrderObject,
    noLimit?: boolean,
  ): Promise<{ total: number; data: T[] }> {
    if (!page || page < 0) page = 0;
    if (!recordsPerPage || recordsPerPage < 0) recordsPerPage = 10;
    if (!noLimit) noLimit = false;

    const pagination = noLimit
      ? {}
      : { skip: page * recordsPerPage, take: recordsPerPage };

    const data = await this.repository.find({
      ...pagination,
      where: {
        ...(filter ||
        this.filterColumns.find((x) => x.column.includes('id_company'))
          ? FilterHelper(filter, this.filterColumns, id_company)
          : ({
              id: Not(IsNull()),
            } as FindOptionsWhere<T>)),
      },
      order: {
        ...OrderHelper(order, this.orderColumns),
      },
    });
    const total = noLimit
      ? null
      : await this.repository.count({
          where: {
            ...(filter ||
            this.filterColumns.find((x) => x.column.includes('id_company'))
              ? FilterHelper(filter, this.filterColumns, id_company)
              : ({
                  id: Not(IsNull()),
                } as FindOptionsWhere<T>)),
          },
          loadEagerRelations: false,
        });
    return { data, total };
  }

Steps to reproduce

try to order an entity with one to many relation and order by its relation date column

My Environment

Dependency	Version
Operating System
Node.js version	x.y.zzz
Typescript version	x.y.zzz
TypeORM version	x.y.zzz

Additional Context

No response

Relevant Database Driver(s)

[ ] aurora-mysql
[ ] aurora-postgres
[ ] better-sqlite3
[ ] cockroachdb
[ ] cordova
[ ] expo
[ ] mongodb
[ ] mysql
[ ] nativescript
[ ] oracle
[X] postgres
[ ] react-native
[ ] sap
[ ] spanner
[ ] sqlite
[ ] sqlite-abstract
[ ] sqljs
[ ] sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.

typeorm / typeorm