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?

144583 次浏览
小开
最佳答案

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.

小开

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

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

小开

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!


提交答案