paginate not working for query builder with selects

[arkarmintun1]

I have a query builder with multiple selects as shown below. But the fields selected in the query builder do not seem to be working with paginate. In the example below, even though there is a column created_at from the query builder, the query failed. It seems like it's due to the PlayerStatistic alias in the ORDER BY. I also tried giving the query builder alias as PlayerStatistic. The query still failing. How can I resolve the issue? Thank you.

 const queryBuilder = this.playerStatisticRepository
      .createQueryBuilder()
      .select('SUM(profit)', 'profit',
      .addSelect("DATE_TRUNC('day', created_at)", 'created_at')
      .groupBy("DATE_TRUNC('day', created_at)")

return paginate(query, queryBuilder, {
      sortableColumns: ['createdAt', 'profit'],
      defaultLimit: 10,
      defaultSortBy: [['createdAt', 'DESC']],
      filterableColumns: { createdAt: [FilterOperator.BTW] },
});

query failed: SELECT SUM(profit) AS "profit", DATE_TRUNC('day', created_at) AS "created_at" FROM "player_statistics" "PlayerStatistic" WHERE "PlayerStatistic"."deleted_at" IS NULL GROUP BY DATE_TRUNC('day', created_at) ORDER BY "PlayerStatistic"."created_at" DESC LIMIT 10
error: error: column "PlayerStatistic.created_at" must appear in the GROUP BY clause or be used in an aggregate function

[alexsvt2]

Our team is facing the same problem but with all queryBuilders

[ppetzold]

def needs fixing. would appreciate some RCA and PR

[ppetzold]

btw. we do support query builder but there is basically no coverage for it. the topic def needs some attention from all angles.

[ppetzold]

is this still an issue with v6+ ?

[pavlyutkin]

I need select user distance

ST_Distance(ST_SetSRID(ST_MakePoint(${user.latitude}, ${user.longitude}), 4326), user.location, true)

but i get an error: syntax error

query console: (SELECT "profile"."id" AS "profile_id", profile.76713, 37.729395, 4326),

[telecare-zeno]

const patientsQueryBuilder = this.patientRepository
      .createQueryBuilder('patient')
      .leftJoinAndSelect(
        'patient.patientDetails',
        'patientDetail',
        'patientDetail.organization_id = :organizationId',
        { organizationId },
      )
      .where('patientDetail.id IS NOT NULL')

await paginate<UniquePatient>(paginationQuery, patientsQueryBuilder, {
      sortableColumns: [
        'firstName',
        'lastName',
        'dateOfBirth',
        'patientDetails.updatedAt',
      ],
      searchableColumns: ['firstName', 'lastName', 'dateOfBirth'],
    });

We also met the same issue, error is "patient_patientDetails_rel" alias was not found. Maybe you forgot to join it?

I'm not sure why the config can not get the relations from query builder, if I add relations in config, such as

await paginate<UniquePatient>(paginationQuery, patientsQueryBuilder, {
      sortableColumns: [
        'firstName',
        'lastName',
        'dateOfBirth',
        'patientDetails.updatedAt',
      ],
      searchableColumns: ['firstName', 'lastName', 'dateOfBirth'],
      relations: ['patientDetails'],
    });

it will not throw an error, but the left join search condition in the query builder will be overwritten. Not sure there is any way can use left join in the config. Or any other ideas?

[telecare-zeno]

const patientsQueryBuilder = this.patientRepository
      .createQueryBuilder('patient')
      .leftJoinAndSelect(
        'patient.patientDetails',
        'patientDetail',
        'patientDetail.organization_id = :organizationId',
        { organizationId },
      )
      .where('patientDetail.id IS NOT NULL')

await paginate<UniquePatient>(paginationQuery, patientsQueryBuilder, {
      sortableColumns: [
        'firstName',
        'lastName',
        'dateOfBirth',
        'patientDetails.updatedAt',
      ],
      searchableColumns: ['firstName', 'lastName', 'dateOfBirth'],
    });

We also met the same issue, error is "patient_patientDetails_rel" alias was not found. Maybe you forgot to join it?

I'm not sure why the config can not get the relations from query builder, if I add relations in config, such as

await paginate<UniquePatient>(paginationQuery, patientsQueryBuilder, {
      sortableColumns: [
        'firstName',
        'lastName',
        'dateOfBirth',
        'patientDetails.updatedAt',
      ],
      searchableColumns: ['firstName', 'lastName', 'dateOfBirth'],
      relations: ['patientDetails'],
    });

it will not throw an error, but the left join search condition in the query builder will be overwritten. Not sure there is any way can use left join in the config. Or any other ideas?

Temporary resolved this issue by changing the alias in query builder with the same format which is patient_patientDetails_rel modified by paginate lib. I saw the issue which made this decision but it still not too much make sense to make the mismatch between queryBuilder and config