DISTINCT in query builder

[dariusmb]

Issue type:

[x] question
[ ] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[x] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

[afurculita]

@dariusmb can you please tell us more about your problem?

[gabrielshawandpartners]

I'm facing an issue with DISTINCT and Query Builder too, I don't know if it's the same that was reported here though, in my case if I do:

const applications = await Application.createQueryBuilder("applications")
    .select('DISTINCT ON (applications.id) applications.id', 'id')
    .addSelect('applications.loan_number', 'loanNumber')
    .addSelect('applicant.id', 'applicantId')
    .addSelect('ass.status', 'signature_status')
    .leftJoin('applications.applicationApplicants', 'applicant')
    .leftJoin('applications.applicationSignatureStatus', 'ass')
    .where("applications.deleted_at = null")
    .orderBy("applications.id", "DESC")
    .limit(4)
    .getRawMany();

The first select is not being placed as the first select, resulting in a syntax error:

SELECT 
"applicant"."id" AS "applicantId",
"ass"."status" AS "signature_status",
DISTINCT ON ("applications"."id") "applications"."id" AS "id",
applications.loan_number AS "loanNumber"
FROM "public"."applications" "applications"
LEFT JOIN "public"."application_applicants" "applicant" ON "applicant"."application_id"="applications"."id" 
ORDER BY "applications"."id" DESC
LIMIT 4

error: { error: syntax error at or near "DISTINCT"

Database system/driver:
[X ] postgres

TypeORM version:
[X] 0.2.7

[afurculita]

Why not simply:

const applications = await Application.createQueryBuilder("applications")
    .select('DISTINCT applications.id', 'id')
    .addSelect('applications.loan_number', 'loanNumber')
    .addSelect('applicant.id', 'applicantId')
    .addSelect('ass.status', 'signature_status')
    .leftJoin('applications.applicationApplicants', 'applicant')
    .leftJoin('applications.applicationSignatureStatus', 'ass')
    .where("applications.deleted_at = null")
    .orderBy("applications.id", "DESC")
    .limit(4)
    .getRawMany();

?

[gabrielshawandpartners]

@afurculita The Postgres DISTINCT ON behaves a bit different from DISTINCT clause, I simplified the query a bit just to post here, but anyway, the same problem happens with DISTINCT

[gabrielshawandpartners]

Wouldn't be interesting if we had a distinct method ? Like in KNEX we have:

knex('tablename').distinct(knex.raw('ON (column) column'))
.orderBy('column')
.orderBy('another_column');

[exoszajzbuk]

Same issue here, the DISTINCT ON select is not getting placed as the first select => syntax error

[ollija]

I made a PR fixing this issue #3065

[koebeen]

@gabrielshawandpartners bit late to the party perhaps but i've found that writing this as one long select works (in the regular branch). may be helpful for people experiencing the same:
const applications = await Application.createQueryBuilder("applications") .select('DISTINCT ON (applications.id) applications.id as id, applications.loan_number' as "loanNumber", applicant.id' as "applicantId", ass.status as "signature_status"') .leftJoin('applications.applicationApplicants', 'applicant') .leftJoin('applications.applicationSignatureStatus', 'ass') .where("applications.deleted_at = null") .orderBy("applications.id", "DESC") .limit(4) .getRawMany();
(sorry for the formatting. just drop this ^ in your editor of choice i suppose :))

[kajalj3098]

select distinct p.id_company as idCompany
FROM [dbo].[company_persons] p inner JOIN [dbo].[companies] c ON p.id_company=c.id_company where id_person=${id}

distinct in query builder is not working for me