Using COUNT in doctrine

Question

I have a following dql in my repostory class:

public function find2($id)
{
    return $this->createQueryBuilder('b')
        ->select('b', 'branch', 'pai', 'dri', 'COUNT(dri) AS ct')
        ->leftJoin('b.branches', 'branch')
        ->leftJoin('branch.productAllocationItems','pai')
        ->leftJoin('pai.DRItems', 'dri')
        ->where('b.id = :id')
        ->setParameter('id', $id)
        ->andWhere('ct > 1')
        ->getQuery()
        ->getResult(); 
}

My problem is COUNT doesnt seem to work, is there any wrong in my query? I just want to return entity whose DRItems based on the condition. Thanks.

I got the following error:

Column not found: 1054 Unknown column 'sclr0' in 'where clause'

herr · Accepted Answer · 2016-01-28 10:00:25Z

1

Use

->having('ct > 1')

instead of

->andWhere('ct > 1')

public function find2($id)
{
    return $this->createQueryBuilder('b')
        ->select('b', 'branch', 'pai', 'dri', 'COUNT(dri) AS ct')
        ->leftJoin('b.branches', 'branch')
        ->leftJoin('branch.productAllocationItems','pai')
        ->leftJoin('pai.DRItems', 'dri')
        ->where('b.id = :id')
        ->setParameter('id', $id)
        ->having('ct > 1')
        ->getQuery()
        ->getResult(); 
}

answered Jan 28, 2016 at 10:00

herr

8471 gold badge8 silver badges21 bronze badges

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

Comments

Noman · Accepted Answer · 2016-01-28 09:47:50Z

0

You can get count by change select statement.

As per documentation select can be use below format

but for count you can achieve with comma separated fields with count , just like we do it in SQL.

// Example - $qb->select('u')
// Example - $qb->select(array('u', 'p'))
// Example - $qb->select($qb->expr()->select('u', 'p'))
public function select($select = null);

Change

->select('b', 'branch', 'pai', 'dri', 'COUNT(dri) AS ct')

To

->select( 'b,branch, pai, dri ,COUNT(dri) AS ct')

answered Jan 28, 2016 at 9:47

Noman

4,1361 gold badge23 silver badges37 bronze badges

Collectives™ on Stack Overflow

Using COUNT in doctrine

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related