symfony and doctrine: select distinct column
by prettyscripts on 2010-06-16 16:28:27
to select distinct column values from a table, there are a few things to note to make sure only distinct rows are returned.
in /path/to/lib/model/doctrine/SomeTable.class.php:
PHP:
public function getDistinctValue() { | |
$q = Doctrine_Query()::create() | |
->select('DISTINCT column_name as some_column') | |
->from('SomeTable'); | |
return $q->fetchArray(); | |
} |
notes:
- line 3: DISTINCT must be capitalized
- line 3: 'as' must be used, without this everything will be returned
- line 5: use fetchArray() instead of execute(), the latter doesn't work!
7 comments
Comment by Tibi @ 2010-09-17 21:12:04
Only works if you retrieve that particular list. If you need any other field, it will not work(e.g. select('DISTINCT column_name as some_column, columnB'). I consider ridiculous for such a big ORM to not be able to implement a standard distinct mechanism.
Comment by nicola @ 2011-01-11 22:50:30
you saved my life.
Comment by Nicolas @ 2011-02-08 20:12:02
I got an example where I needed to use multiple distinct criteria, and it worked this way:
->select('DISTINCT column_a as column_a, column_b as column_b, column_c as column_c)
But, as noticed, it fits the fetchArray. And how could we do if we want records as a return type ?
->select('DISTINCT column_a as column_a, column_b as column_b, column_c as column_c)
But, as noticed, it fits the fetchArray. And how could we do if we want records as a return type ?
Making it with brackets defines the column itself distinct!!, so:
Doctrine_Query::create()->select('DISTINCT (c.type) as type')->from('Client c')->fetchArray().
Good luck!
Doctrine_Query::create()->select('DISTINCT (c.type) as type')->from('Client c')->fetchArray().
Good luck!
Comment by Cristian @ 2011-07-22 00:54:37
Why don't you use distinct() from Doctrine_Query?
Doctrine_Query::create()
->select('c.type')
->distinct()
->from(...)
Doctrine_Query::create()
->select('c.type')
->distinct()
->from(...)
Comment by Daniel @ 2011-10-12 19:57:25
Thanks so much for this, it saved my day.
Comment by Web Hosting @ 2011-10-16 04:32:52
Thanks for your post! I am now looking how to use a "DISTINCT ON (column)" with Doctrine2 in Symfony2. Do you have any ideas?
Comment feed for this postLeave a comment
Affiliates
©2012 by prettyscripts.• powered by b2evolution • skin by prettyscripts