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- gt createQueryBuilder  cc       - gt Where  cc contenttype    type       - gt setParameter  type    blogarticle       - gt getQuery      categories    category- gt 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- gt createQueryBuilder  cc       - gt Where  cc contenttype    type       - gt setParameter  type    blogarticle       - gt distinct  cc categoryid       - gt getQuery      categories    category- gt 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

User · Answer

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!

User · Answer

This works   category    catrep- gt createQueryBuilder  cc           - gt select  cc categoryid           - gt where  cc contenttype    type           - gt setParameter  type    blogarticle           - gt distinct           - gt getQuery      categories    category- gt getResult     Edit for Symfony 3  amp  4  You should use - gt groupBy  cc categoryid   instead of - gt distinct

User · Answer

If you use the  select    statement  you can do this    category    catrep- gt createQueryBuilder  cc       - gt select  DISTINCT cc contenttype       - gt Where  cc contenttype    type       - gt setParameter  type    blogarticle       - gt getQuery      categories    category- gt getResult

User · Answer

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

[php] How to select distinct query using symfony2 doctrine query builder?

Examples related to php

Examples related to doctrine-orm

Examples related to symfony

Examples related to query-builder