QueryBuilder and DISTINCT ON (fields) in Postgresql

Question

QueryBuilder and DISTINCT ON (fields) in Postgresql

Alexey Dec '15

Created Dec '15	Last Reply Jan '17	Replies 1	Views 612	Votes 0

Alexey 762

Dec '15

Hello everybody! I need to make postgresql query SELECT DISTINCT ON (field1) field2, field3.. using QueryBuilder

The raw working query looks like this:

SELECT DISTINCT ON (report.plu) report.plu, products.title FROM report INNER JOIN products ON report.plu = products.plu ORDER BY report.plu;

I have join to products table, where I need to get product title, but the problem is in many similar products with same plu(ids), titles, etc and different barcodes. So the resulting query returns too many rows.

Maybe I can somehow rewrite final query from QueryBuilder with all conditions by adding SELECT DISTINCT ON in the beggining?

I've tried to make subquery in phalcon join on codition statement but havn't got needed results.

I've also tried to use simple $queryBuilder->distinct(true), which helps, but breaks whole sorting mechanism..

Thanks for help!

Lenar · Answer 1 · 2017-01-10T05:29:57-07:00

Lenar
7.0k

Jan '17

phalcon cannot work well with postgresql ))