查询生成器:多个 AND 充当 OR

Question

我在实体内容和过滤器之间有多对多的关系。我想获取所有具有过滤器"1"、"2"和"3"(所有三个(的内容。我的查询给我的结果就像我会使用"OR"一样，因为我得到很多内容，这些内容只有三个过滤器中的一个。

我的查询：

public function getContentByFilters($categ, $filter, $filter2, $filter3){
    $query = $this->createQueryBuilder('c')
    ->leftJoin('c.filterfilter', 'f')
    ->where('f.idfilter = :filter_idfilter')
    ->setParameter('filter_idfilter', $filter)
    ->andWhere('f.idfilter = :filter_idfilter')
    ->setParameter('filter_idfilter', $filter2)
    ->andWhere('f.idfilter = :filter_idfilter')
    ->setParameter('filter_idfilter', $filter3)
    ->andWhere('c.contentCategorycontentCategory = ?2')
    ->setParameter(2, $categ)
    ->getQuery()->getResult();
    return $query;
}

实体内容：

/**
 * Content
 *
 * * @ORMEntity(repositoryClass="loicContentBundleEntityContentRepository")
 * @ORMTable(name="content", uniqueConstraints={@ORMUniqueConstraint(name="idcontent_UNIQUE", columns={"idcontent"})}, indexes={@ORMIndex(name="user_id", columns={"user_id"}), @ORMIndex(name="fk_content_content_category1_idx", columns={"content_category_idcontent_category"})})
 */
class Content
{
    /**
     * @var integer
     *
     * @ORMColumn(name="idcontent", type="integer", nullable=false)
     * @ORMId
     * @ORMGeneratedValue(strategy="IDENTITY")
     */
    private $idcontent;
.............
/**
 * @var DoctrineCommonCollectionsCollection
 *
 * @ORMManyToMany(targetEntity="loicFilterBundleEntityFilter", inversedBy="contentcontent")
 * @ORMJoinTable(name="content_has_filter",
 *   joinColumns={
 *     @ORMJoinColumn(name="content_idcontent", referencedColumnName="idcontent")
 *   },
 *   inverseJoinColumns={
 *     @ORMJoinColumn(name="filter_idfilter", referencedColumnName="idfilter")
 *   }
 * )
 */
private $filterfilter;
/**
 * Constructor
 */
public function __construct()
{
    $this->contentLinked = new DoctrineCommonCollectionsArrayCollection();
    $this->filterfilter = new DoctrineCommonCollectionsArrayCollection();
    $this->creationDate = new DateTime();
}
.........
/**
 *
 * @return the DoctrineCommonCollectionsCollection
 */
public function getFilterfilter() {
    return $this->filterfilter;
}
/**
 *
 * @param
 *          $filterfilter
 */
public function setFilterfilter($filterfilter) {
    $this->filterfilter = $filterfilter;
    return $this;
}

}

实体筛选器：

 use DoctrineORMMapping as ORM;
    /**
     * Filter
     *
     * * @ORMEntity(repositoryClass="loicFilterBundleEntityFilterRepository")
     * @ORMTable(name="filter", uniqueConstraints={@ORMUniqueConstraint(name="idfilter_UNIQUE", columns={"idfilter"})}, indexes={@ORMIndex(name="fk_filter_filter_category1_idx", columns={"filter_category_idfilter_category"})})
     */
    class Filter
    {
        /**
         * @var integer
         *
         * @ORMColumn(name="idfilter", type="integer", nullable=false)
         * @ORMId
         * @ORMGeneratedValue(strategy="IDENTITY")
         */
        private $idfilter;
    ............

        /**
         * @var DoctrineCommonCollectionsCollection
         *
         * @ORMManyToMany(targetEntity="loicContentBundleEntityContent", mappedBy="filterfilter")
         */
        private $contentcontent;
    .....
      /**
         * Constructor
         */
        public function __construct()
        {
            $this->contentcontent = new DoctrineCommonCollectionsArrayCollection();
            $this->user = new DoctrineCommonCollectionsArrayCollection();
            $this->status = 1;
        }
    ......
        /**
         *
         * @return the DoctrineCommonCollectionsCollection
         */
        public function getContentcontent() {
            return $this->contentcontent;
        }
        /**
         *
         * @param
         *          $contentcontent
         */
        public function setContentcontent($contentcontent) {
            $this->contentcontent = $contentcontent;
            return $this;
        }

Answer 1

由于您要检查 3 个过滤器，因此还需要JOIN 3 次。就好像你有 3 个不同的 ManyToMany 关系，只需要一行，符合 3 个特定要求 - 唯一的区别是，你加入了同一个表。未经测试，但它应该像这样工作

$query = $this->createQueryBuilder('c')
    ->join('c.filterfilter', 'f1')
    ->join('c.filterfilter', 'f2')
    ->join('c.filterfilter', 'f3')
    ->where('f1.idfilter = :filter_idfilter1')
    ->andWhere('f2.idfilter = :filter_idfilter2')
    ->andWhere('f3.idfilter = :filter_idfilter3')
    ->andWhere('c.contentCategorycontentCategory = :category')
    ->setParameters(array(
        'filter_idfilter1' => $filter,
        'filter_idfilter2' => $filter2,
        'filter_idfilter3' => $filter3,
        'category' => $categ,
    ))
    ->getQuery()->getResult();

内部联接可能更好，因为您只需要满足这些要求的行。另请注意，使用过多联接通常被认为是不好的做法，可能会降低性能。首选解决方案通常是构造特定视图而不是多个联接。