主义2 manyToMany关系用AND选择实体 [英] Doctrine 2 manyToMany relation Selecting Entity with AND

问题描述

我正在建立一个需要根据自己的选择过滤别墅的网站。例如：别墅有选择：Wi-Fi，Pool，Animals允许。

I'm building a website which needs the ability to filter villa's based on their options. So for example: A villa has the options: Wi-Fi, Pool, Animals allowed.

过滤时，我提供选项：Wi-Fi和Pool。我现在需要一个基于BOTH这些选项的过滤的别墅列表。所以我只想要同时拥有Wi-Fi和泳池的别墅。

When filtering I'm giving the options: Wi-Fi and Pool. I now need a filtered list of villa's based on BOTH these options. So I only want the villa's that have both Wi-Fi AND a Pool.

我的别墅实体如下所示：

My Villa Entity looks like this:

class Object
{
/**
 * @var integer
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var string
 *
 * @ORM\Column(name="name", type="string", length=255)
 */
private $name;

/**
 * @ORM\manyToMany(targetEntity="Option", mappedBy="objects")
 */
private $options;

//...
}

我的选项实体看起来像这样：

And my Option Entity looks like this:

class Option
{

/**
 * @var integer
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var string
 *
 * @ORM\Column(name="name", type="string", length=255)
 */
private $tag;

/**
 * @ORM\manyToMany(targetEntity="Object", inversedBy="options")
 * @ORM\JoinTable(name="Object_Options")
 */
private $objects;

//...
}

我有一个过滤器ID数组 $ filters ，我需要进行过滤。

I have an array of filter ids $filters with which I need to filter.

我似乎无法弄清楚如何完成这个。

I just can't seem to figure out how to accomplish this.

我现在有这个

$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select("o")->from("Object", "o");

if ($filters && count($filters) > 0) {
    $qb->innerJoin("o.options", "f");
    $qb->andWhere($qb->expr()->in("f.id", $filters));
}

$query = $qb->getQuery();

但这实际上与我需要的相反。这将根据选项过滤别墅，但使用OR而不是AND。所以这给我一个别墅有Wi-Fi或一个池的结果。

but this actually does the opposite of what I need. This filters villa's based on the options but uses OR instead of AND. So this gives me results for where a Villa has Wi-Fi OR a Pool.

所以在伪代码中我需要：

So in pseudo code I need:

get all Objects that have every given option (and maybe more)

>

推荐答案

SOLVED ：

弄清楚了。我正在跟踪我的 IN 语句，但它需要更多的盐。显然，在您选择了所有选项后，您需要检查过滤器的数量是否与您提供的过滤器数量相同。当我在MySQL中一步步尝试时，这实际上是有意义的。

Allright, so I figured it out. I was on the right track with my IN statement, but it needed a bit more salt. Apparently after you have selected all the options, you need to check if the amount of filters is the same as the amount of filters you've given. Which actually made sense when i tried this step by step in MySQL.

$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select("DISTINCT o")->from("Object", "o");

if ($filters && count($filters) > 0) {
    $qb->innerJoin("o.options",
                   "f", 
                   "WITH", 
                   "f.id IN (:filters)")->setParameter("filters", $filters);

    $qb->groupBy("o.id");

    $qb->having("COUNT(DISTINCT f.id) = :count")
       ->setParameter("count", count($filters));
}

这篇关于主义2 manyToMany关系用AND选择实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！