错误：'c'在其声明范围之外使用

Question

An application has a page of statistics representing dozens of calculations. To avoid duplicating code in a repository the error

Error: 'c' is used outside the scope of its declaration

occurs when attempting to insert DQL with conditions into a QueryBuilder.

The basic entities include Household and Contact. Calculations are based on contact date range, site (location of contact), and type (type of contact). There is a service that creates an array of where clauses and query parameters, as will be evident in the code below.

I know the calculation works if all the code occurs in a single function. It seems the problem arises from the join with the Contact entity and its necessary constraints. Can DRY be accomplished in this scenario?

All of the following appear in the Household entity's repository.

The DQL is:

private function reportHousehold($criteria)
{
    return $this->createQueryBuilder('i')
        ->select('i.id')
            ->join('TruckeeProjectmanaBundle:Contact', 'c', 'WITH',
                'c.household = i')
            ->where($criteria['betweenWhereClause'])
            ->andWhere($criteria['siteWhereClause'])
            ->andWhere($criteria['contactWhereClause'])
            ->getDQL()
    ;
}

Example of $criteria: $criteria['betweenWhereClause'] = 'c.contactDate BETWEEN :startDate AND :endDate'

One of the calculations on Household:

public function res($criteria)
{
    $parameters = array_merge(
        $criteria['betweenParameters'], $criteria['siteParameters'],
        $criteria['startParameters'], $criteria['startParameters'],
        $criteria['contactParameters']);
    $qb = $this->getEntityManager()->createQueryBuilder();

    return $this->getEntityManager()->createQueryBuilder()
            ->select('h.id, 12*(YEAR(:startDate) - h.arrivalyear) + (MONTH(:startDate) - h.arrivalmonth) Mos')
            ->from('TruckeeProjectmanaBundle:Household', 'h')
            ->distinct()
//DQL inserted here:
            ->where($qb->expr()->in('h.id', $this->reportHousehold($criteria)))
            ->andWhere($qb->expr()->isNotNull('h.arrivalyear'))
            ->andWhere($qb->expr()->isNotNull('h.arrivalmonth'))
            ->andWhere($criteria['startWhereClause'])
            ->setParameters($parameters)
            ->getQuery()->getResult()
    ;
}

Answer 1

In a sense Preciel is correct: the solution does require the use of $this->getEntityManager()->createQueryBuilder(). Instead of injecting DQL as a subquery, the trick is to return an array of ids and use the array in an IN clause. The effect is to remove any consideration of entities other than the Household entity from the calculation. Here's the result:

public function res($criteria)
{
    $parameters = array_merge($criteria['startParameters'], $criteria['startParameters'], ['hArray' => $this->reportHousehold($criteria)]);
    $qb = $this->getEntityManager()->createQueryBuilder();

    return $this->getEntityManager()->createQueryBuilder()
            ->select('h.id, 12*(YEAR(:startDate) - h.arrivalyear) + (MONTH(:startDate) - h.arrivalmonth) Mos')
            ->from('TruckeeProjectmanaBundle:Household', 'h')
            ->distinct()
            ->where('h.id IN (:hArray)')
            ->andWhere($qb->expr()->isNotNull('h.arrivalyear'))
            ->andWhere($qb->expr()->isNotNull('h.arrivalmonth'))
            ->setParameters($parameters)
            ->getQuery()->getResult()
    ;
}

private function reportHousehold($criteria)
{
    $parameters = array_merge($criteria['betweenParameters'], $criteria['siteParameters'], $criteria['contactParameters']);
    return $this->createQueryBuilder('i')
            ->select('i.id')
            ->join('TruckeeProjectmanaBundle:Contact', 'c', 'WITH', 'c.household = i')
            ->where($criteria['betweenWhereClause'])
            ->andWhere($criteria['siteWhereClause'])
            ->andWhere($criteria['contactWhereClause'])
            ->setParameters($parameters)
            ->getQuery()->getResult()
    ;
}