Adding a Doctrine Admission Proposal

Question

I'm new to Doctrine, and I'm trying to figure out how to add a sentence about my expression. Basically, I want to be able to filter on returned items depending 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: An expression like Doctrine \ ORM \ QueryBuilder is not allowed in this context.

Without the having () clause, everything works fine.

I do not know how to solve this.

Answer 1

HAVING clause requires GROUP BY. In the doctrine it would be like this:

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

Assuming you are using mysql, here is a suggestion tutorial: http://www.mysqltutorial.org/mysql-having.aspx

Answer 2

Perhaps you need to bind number 3 to a parameter:

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

Answer 3

Purpose: to filter. On the one hand, where we have some known summable conditions that we want to filter out (for example, the score of the original parts in a bunch) on many sides of the O2M relationship, where they want to limit one side along with some other selection criteria.

Then we add several conditions for the LEFT_JOIN operation: Condition No. 1 - identifier bundle.id == original_part.bundle. Condition No. 2 - Original_part. PartStatusType == 3 (some solid value).

Then we filter to COUNT (op)> = 1, which gives our more limited answer, which works great 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 an alias for One (bundle object).