Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to select distinct query using symfony2 doctrine query builder?

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?

like image 665
mickburkejnr Avatar asked Aug 25 '11 09:08

mickburkejnr


3 Answers

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()

like image 129
M. Foti Avatar answered Oct 22 '22 10:10

M. Foti


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();
like image 57
Chris Avatar answered Oct 22 '22 11:10

Chris


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.

like image 31
Raffael Avatar answered Oct 22 '22 11:10

Raffael