How to use "DISTINCT ON (field)" in Doctrine 2?

Question

I know how to use "DISTINCT" in Doctrine 2, but I really need to use "DISTINCT ON (field)" and I don't know how to do this with the QueryBuilder.

My SQL query looks like:

SELECT DISTINCT ON (currency) currency, amount FROM payments ORDER BY currency

And this query works perfect, but I can't use it with the QueryBuilder. Maybe I could write this query on some other way?

Answer 1

I would suggest that the SELECT DISTINCT ON (..) construct that PostgreSQL supports is outside the Object Relational Model (ORM) that is central to Doctrine. Or, perhaps put another way, because SELECT DISTINCT ON (..) is rare in SQL implementations Doctrine haven't coded for it.

Regardless of the actual logic for it not working, I would suggest you try Doctrine's "Native SQL". You need to map the results of your query to the ORM.

With NativeQuery you can execute native SELECT SQL statements and map the results to Doctrine entities or any other result format supported by Doctrine.

In order to make this mapping possible, you need to describe to Doctrine what columns in the result map to which entity property. This description is represented by a ResultSetMapping object.

With this feature you can map arbitrary SQL code to objects, such as highly vendor-optimized SQL or stored-procedures.

SELECT DISTINCT ON (..) falls into vendor-optimized SQL I think, so using NativeQuery should allow you to access it.