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?

Signpost

You should be passing a boolean value to the distinct() function. doctrine-project.org/api/orm/2.2/… – Hellenhellene

Answer 1

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.

Ferrate

@Signpost Until You add an ORDER clause. :( – Teahouse

@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? – Signpost

@Signpost Grouping happens before ordering in SQL. – Teahouse

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. – Wymore

(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'); – Acedia

This sholdn't be an answer for the question – Nip

Answer 2

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

Raja

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 – Corn

It's extremely bad practice to use 'group by' and it can throw an error "GROUP BY clause; this is incompatible with sql_mode=only_full_group_by" – Allegorist

Answer 3

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

Peterpeterborough

Answer 4

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!

Hm

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? – Treytri

@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. – Hm