How to use countDistinct in Doctrine query builder (Symfony)

Question

I am trying to count the distinct number of IDs returned for a query with his:

$query = $repo->createQueryBuilder('prov')
        ->select('c.id')
        ->innerJoin('prov.products', 'prod')
        ->innerJoin('prod.customerItems', 'ci')
        ->innerJoin('ci.customer', 'c')
        ->where('prov.id = :brand')
        ->setParameter('brand', $brand)
        ->countDistinct('c.id')
        ->getQuery();

I am getting this error though:

Attempted to call method "countDistinct" on class "Doctrine\ORM\QueryBuilder" [...]

I have also tried

$query = $repo->createQueryBuilder('prov')
        ->select('c.id')
        ->innerJoin('prov.products', 'prod')
        ->innerJoin('prod.customerItems', 'ci')
        ->innerJoin('ci.customer', 'c')
        ->where('prov.id = :brand')
        ->setParameter('brand', $brand)
        ->expr()->countDistinct('c.id')
        ->getQuery();

which leads to this error:

Error: Call to a member function getQuery() on a non-object in

I can't get any other pointers as to how to do this differently from the documentation

Kamil Adryjanek · Accepted Answer · 2014-08-05 12:55:22Z

35

countDistinct is method of Expr class and COUNT DISTINCT need to be in SELECT statement so:

$qb = $repo->createQueryBuilder('prov');
$query = $qb
        ->select($qb->expr()->countDistinct('c.id'))
        ->innerJoin('prov.products', 'prod')
        ->innerJoin('prod.customerItems', 'ci')
        ->innerJoin('ci.customer', 'c')
        ->where('prov.id = :brand')
        ->setParameter('brand', $brand)
        ->getQuery();

should work. Or simply:

$query = $repo->createQueryBuilder('prov')
        ->select('COUNT(DISTINCT c.id)')
        ->innerJoin('prov.products', 'prod')
        ->innerJoin('prod.customerItems', 'ci')
        ->innerJoin('ci.customer', 'c')
        ->where('prov.id = :brand')
        ->setParameter('brand', $brand)
        ->getQuery();

answered Aug 5, 2014 at 12:55

Kamil Adryjanek

3,3481 gold badge22 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeet Over a year ago

Thanks, But how come DISTINCT works as a function when used with SUM but doesn't work the same with COUNT ? Any information ?

Tomasz Madeyski · Accepted Answer · 2014-08-05 12:40:12Z

3

Proper way to use countDistinct in your case is:

$qb = $repo->createQueryBuilder('prov');

$query = $qb->
    ->select($qb->expr()->countDistinct('c.id'))
    ->innerJoin('prov.products', 'prod')
    ->innerJoin('prod.customerItems', 'ci')
    ->innerJoin('ci.customer', 'c')
    ->where('prov.id = :brand')
    ->setParameter('brand', $brand)
    ->getQuery();

answered Aug 5, 2014 at 12:40

Tomasz Madeyski

10.9k3 gold badges54 silver badges66 bronze badges

Collectives™ on Stack Overflow

How to use countDistinct in Doctrine query builder (Symfony)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related