教义QueryBuilder：ManyToOne关系，其中多个子实体必须匹配 [英] Doctrine QueryBuilder: ManyToOne Relationship where more than one subEntity must match

问题描述

我正在尝试进行查询，其中我有一个实体 Job 和一个实体 JobProperty 其中1 Job 可以有很多 JobProperties 。

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

使用具有Symfony2的Doctrine查询多对多关系说明了如何根据其实体的subEntities值检索匹配项。由此我建立了查询：

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.

推荐答案

您没有正确执行操作，因为标签和值与最后一个过滤器值匹配，因为占位符：label ，：value 在每次循环迭代中都不唯一，因此loop生成的所有子句都将与最后一个标签匹配和值。

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.

首先它将所有标签和值收集在单独的数组中，然后将使用 IN（）操作将其与作业的属性匹配，最后获得作业其属性与您需要进行聚合以计算匹配结果所需的所有过滤器相匹配，并且应等于过滤器的计数

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();

这里是另一个答案作为参考，与您的问题相似
Symfony2-Doctrine2 QueryBuilder在ManyToMany字段中

这篇关于教义QueryBuilder：ManyToOne关系，其中多个子实体必须匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！