Adding a Having Clause to Doctrine Statement

Question

I am new to Doctrine and I am trying to figure out how to add a having clause on my statement. Basically I want to be able to filter down on items returned based on how many attributes the user selects. The code is as follows:

// create query builder
    $qb = $this->getEntityManager()->createQueryBuilder();

    $qb->select('p')
        ->from($this->_entityName, 'p')
        ->leftJoin('p.options', 'o')
        ->where('p.active = :active')
        ->setParameter('active', 1);

    // add filters
     $qb->leftJoin('o.attributes', 'a');
                $ands = array();

                foreach ($value as $id => $values)
                { echo count($values);
                    $ands[] = $qb->expr()->andX(
                        $qb->expr()->eq('a.attribute_id', intval($id)),
                        $qb->expr()->in('a.attribute_value_id', array_map('intval', $values))
                        $qb->having('COUNT(*)=3) // THIS DOESN'T WORK
                        //$qb->expr()->having('COUNT(*)=3) // THIS DOESN'T WORK EITHER
                    );
                }

                $where = $qb->expr()->andX();


                foreach ($ands as $and)
                {
                    $where->add($and);
                }
                $qb->andWhere($where);
                $result = $qb->getQuery()->getResult();
                return $result;

When I try to execute the statement with the having() clause I get this error: Expression of type 'Doctrine\ORM\QueryBuilder' not allowed in this context.

Without the having() clause everything works perfectly.

I have no idea how to solve this.

Answer 1

HAVING clause requires a GROUP BY. In doctrine it would be something like that:

$qb->groupBy('p.id'); // or use an appropriate field
$qb->having('COUNT(*) = :some_count');
$qb->setParameter('some_count', 3);

Assuming you're using mysql, here is a having clause tutorial: http://www.mysqltutorial.org/mysql-having.aspx

Answer 2

Perhaps you should bind number 3 to a parameter:

$qb->having('COUNT(*)=:some_count')
$qb->setParameter('some_count',3)

Answer 3

Goal: filter down The one side where we have some known summable conditions we want to filter by (e.g., the count of Original Parts in a Bundle) on the many side of a O2M relationship wehere want to limit the One side along with some other criteria to select on.

We are then adding in a few conditions for the LEFT_JOIN operation: Condition #1 - the bundle.id == the original_part.bundle ID. Condition #2 - The original_part's partStatusType == 3 (some hard-coded value).

Then we filter down to the COUNT(op) >= 1 which gives our more limited response that works just fine with pagination.

    $qb->leftJoin(OriginalPart::class, 'op', Expr\Join::WITH,
        $qb->expr()->andX(
            $qb->expr()->eq('op.bundle', 'row.id'),
            $qb->expr()->eq('op.partStatusType', 3)));
    $qb->groupBy('row.id');
    $qb->having($qb->expr()->gte('COUNT(op)', 1));

row is the alias for the One (bundle entity).