Doctrine QueryBuilder: ManyToOne Relationship where more than one subEntity must match

Question

I am trying to do a query where I have an entity Job and an entity JobProperty where 1 Job can have many JobProperties.

Query on a many-to-many relationship using Doctrine with Symfony2 explains how to retrieve matches on one entity based on values of it's subEntities. From this I have built the query:

$qb = $this->getDoctrine()->getRepository('AppBundle:Job')->createQueryBuilder('job')
->innerJoin('job.properties','property');

foreach($filters as $label => $value)
{
    $qb->andWhere('property.label = :label AND property.value = :value')
    ->setParameter('label',$label)
    ->setParameter('value',$value);
}

The query above works to an extent, but it provides results where a property matches against ANY of the filters, and does not only provide results where ALL filters are matched. I need it to return only results where ALL filters are matched.

I might have to go about this in a different way, but I'm not sure I would implement.

Answer 1

You are not doing it correctly you are matching labels and values with the last filter values because the placeholders :label , :value used in query are not unique for each loop iteration, so all the clauses generated by loop will match with the last label and value.

To get the jobs whose each property match with the provided filters you can write somewhat like below doctrine query.

First it will collect all labels and values in separate array and then it will match with properties of job by using IN() operation, in last to get the jobs whose properties matches all the filters you need to build aggregation to count the matching results and should be equal to the count of filters

$qb =  $this->getDoctrine()
            ->getRepository('AppBundle:Job')
            ->createQueryBuilder('job')
            ->innerJoin('job.properties','p');
$labels = array();
$values = array();
foreach($filters as $label => $value)
{
    $labels[] = $label;
    $values[] = $value;
}
$qb->addSelect('COUNT(DISTINCT  p.id) AS total_properties')
   ->andWhere('p.label IN (:labels)')
   ->andWhere('p.value IN (:values)')
   ->addGroupBy('job.id')
   ->having('total_properties = '.count($filters))
   ->setParameter('labels',$labels)
   ->setParameter('values',$values)
   ->getQuery()
   ->getResult();

Here is another answer as a reference here which is similar to your question Symfony2 - Doctrine2 QueryBuilder WHERE IN ManyToMany field