How to select distinct query using symfony2 doctrine query builder?

Question

I have this symfony code where it retrieves all the categories related to a blog section on my project:

$category = $catrep->createQueryBuilder('cc')
    ->Where('cc.contenttype = :type')
    ->setParameter('type', 'blogarticle')
    ->getQuery();

$categories = $category->getResult();

This works, but the query includes duplicates:

Test Content
Business
Test Content

I want to use the DISTINCT command in my query. The only examples I have seen require me to write raw SQL. I want to avoid this as much as possible as I am trying to keep all of my code the same so they all use the QueryBuilder feature supplied by Symfony2/Doctrine.

I tried adding distinct() to my query like this:

$category = $catrep->createQueryBuilder('cc')
    ->Where('cc.contenttype = :type')
    ->setParameter('type', 'blogarticle')
    ->distinct('cc.categoryid')
    ->getQuery();

$categories = $category->getResult();

But it results in the following error:

Fatal error: Call to undefined method Doctrine\ORM\QueryBuilder::distinct()

How do I tell symfony to select distinct?

You should be passing a boolean value to the distinct() function. http://www.doctrine-project.org/api/orm/2.2/class-Doctrine.ORM.QueryBuilder.html#_distinct — Omn, Mar 19 '15 at 22:34

score 192 · Answer 1 · edited Jan 05 '21 at 11:20

192

This works:

$category = $catrep->createQueryBuilder('cc')
        ->select('cc.categoryid')
        ->where('cc.contenttype = :type')
        ->setParameter('type', 'blogarticle')
        ->distinct()
        ->getQuery();

$categories = $category->getResult();

Edit for Symfony 3 & 4.

You should use ->groupBy('cc.categoryid') instead of ->distinct()

edited Jan 05 '21 at 11:20

MaxiGui

6,190
4
16
33

answered Oct 22 '13 at 16:32

M. Foti

3,156
2
16
14

1

Why is that exactly? It looks like it behaves differently. Sometimes we want ->distinct(). When you want to "iterate" over a query with "->toIterable()" while having a join with a ...ToMany relation in the query, then the groupBy is not enough. It must be ->distinct() for the ->toIterable() to work – Julesezaar Feb 18 '22 at 08:10

Chris · Answer 2 · 2012-05-08T06:45:18.880

57

If you use the "select()" statement, you can do this:

$category = $catrep->createQueryBuilder('cc')
    ->select('DISTINCT cc.contenttype')
    ->Where('cc.contenttype = :type')
    ->setParameter('type', 'blogarticle')
    ->getQuery();

$categories = $category->getResult();

edited May 08 '12 at 06:45

answered Jan 25 '12 at 10:04

Chris

4,204
1
25
30

Raffael · Accepted Answer · 2011-08-25T13:38:46.367

31

you could write

select DISTINCT f from t;

as

select f from t group by f;

thing is, I am just currently myself getting into Doctrine, so I cannot give you a real answer. but you could as shown above, simulate a distinct with group by and transform that into Doctrine. if you want add further filtering then use HAVING after group by.

edited Aug 25 '11 at 13:38

answered Aug 25 '11 at 13:28

Raffael

19,547
15
82
160

1

@mickburkejnr Until You add an ORDER clause. :( – undefined Oct 31 '12 at 13:24
1

@xyu I know, it's not ideal really is it? Why should we be lumbered with something like this? Why can't it just work? – mickburkejnr Nov 02 '12 at 15:20
1

@mickburkejnr Grouping happens before ordering in SQL. – undefined Nov 04 '12 at 19:16
12

This is only the correct answer if you are using a sql statement and not the Query Builder. This should not be marked as the answer the solution given by @skler is the correct. – Tom T Oct 04 '14 at 16:38
2

(Old comment, I know...). @Tom T: Actually, this answer is valid. It is saying that instead of trying to use the *actual* `DISTINCT` keyword, simulate it by using `GROUP BY`, in QueryBuilder. He failed to give an example, however. Here's a simple one: `$q = $db->createQueryBuilder(); $q->select('f')->from('t', 't')->groupBy('f');` – user5670895 Nov 13 '15 at 18:45
1

This sholdn't be an answer for the question – Braian Mellor Nov 30 '17 at 15:06

Ali · Answer 4 · 2019-12-24T07:50:49.333

-1

Just open your repository file and add this new function, then call it inside your controller:

 public function distinctCategories(){
        return $this->createQueryBuilder('cc')
        ->where('cc.contenttype = :type')
        ->setParameter('type', 'blogarticle')
        ->groupBy('cc.blogarticle')
        ->getQuery()
        ->getResult()
        ;
    }

Then within your controller:

public function index(YourRepository $repo)
{
    $distinctCategories = $repo->distinctCategories();


    return $this->render('your_twig_file.html.twig', [
        'distinctCategories' => $distinctCategories
    ]);
}

Good luck!

edited Dec 24 '19 at 07:50

answered Dec 20 '19 at 16:00

Ali

216
2
11

What do you mean by "none worked"? What happens instead? Why should that be a problem of Symfony, while the query builder is part of Doctrine? – Nico Haase Dec 20 '19 at 22:30
@NicoHaase, because creating a new Symfony project via composer will install the newest version of Twig and Doctrine, that's why I mentioned that my solution is tested with Symfony5 and whatever Doctrine version comes with it. Hope it is more clear now. – Ali Dec 21 '19 at 07:28

How to select distinct query using symfony2 doctrine query builder?

4 Answers4

Edit for Symfony 3 & 4.

Linked