We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

QueryBuilder and DISTINCT ON (fields) in Postgresql

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!



7.0k

phalcon cannot work well with postgresql ))