symfony and doctrine: select distinct column

by prettyscripts on 2010-06-16 16:28

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!

8 comments

Comment on 2010-09-17 21:12:04 by Tibi

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 on 2011-01-11 22:50:30 by nicola

you saved my life.

Comment on 2011-02-08 20:12:02 by Nicolas

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 ?

Comment on 2011-06-30 22:55:59 by lambertb

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!

Comment on 2011-07-22 00:54:37 by Cristian

Why don't you use distinct() from Doctrine_Query?

Doctrine_Query::create()
->select('c.type')
->distinct()
->from(...)

Comment on 2011-10-12 19:57:25 by Daniel

Thanks so much for this, it saved my day.

Comment on 2011-10-16 04:32:52 by Web Hosting

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 on 2012-09-15 19:14:42 by parisa

Thanks so much!

Comment feed for this post

	`public function getDistinctValue() {`
	`$q = Doctrine_Query()::create()`
	`->select('DISTINCT column_name as some_column')`
	`->from('SomeTable');`
	`return $q->fetchArray();`
	`}`

8 comments

Leave a comment