12

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?

pronngo
  • 820
  • 11
  • 26
  • I'm not familiar with "DISTINCT ON" syntax. Is that similar to GROUP BY? – Daniel Feb 18 '15 at 22:35
  • 2
    @daniel: http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT –  Apr 19 '15 at 20:48
  • You can read this post : http://stackoverflow.com/questions/23246939/zf2-doctrine-2-objectselect-with-distinct-on-field – gtzinos May 18 '15 at 16:12
  • I think using query builder the answer is here [enter link description here][1] [1]: http://stackoverflow.com/questions/7188219/how-to-select-distinct-query-using-symfony2-doctrine-query-builder – dhara Jul 10 '15 at 17:27

3 Answers3

3

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.

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
0

Doctrine QueryBuilder has some limitations. Even if I didn't check if it's was possible with query builder, I do not hesitate to use DQL when I do not know how to write the query with query builder.

Check theses examples at http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html#dql-select-examples

Hope this help.

BADAOUI Mohamed
  • 2,146
  • 1
  • 17
  • 14
0

INDEX BY can be used in DQL, allowing first result rows indexed by the defined string/int field to be overwritten by following ones with the same index:

SELECT 
    p.currency, 
    p.amount 
FROM Namespace\To\Payments p INDEX BY p.currency 
ORDER BY p.currency ASC

DQL - EBNF - INDEX BY

goulashsoup
  • 2,639
  • 2
  • 34
  • 60