symfony and doctrine: select distinct column

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:

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!

9 thoughts on “symfony and doctrine: select distinct column

  1. Tibi Reply

    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.

  2. Nicolas Reply

    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 ?

  3. lambertb Reply

    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!

  4. Cristian Reply

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

    Doctrine_Query::create()
    ->select(‘c.type’)
    ->distinct()
    ->from(…)

  5. Web Hosting Reply

    Thanks for your post! I am now looking how to use a “DISTINCT ON (column)” with Doctrine2 in Symfony2. Do you have any ideas?

Leave a Reply

Your email address will not be published. Required fields are marked *