Query builder: multiple AND act like an OR

Question

I have a many to many relation between entities Content and Filter. I would like to get all Contents which have the filters "1", "2" AND "3" (all three). My query gives me results as if I would use "OR, because I get a lot of contents which have only one of the three Filter.

My query:

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;
}

Entity Content:

/**
 * Content
 *
 * * @ORM\Entity(repositoryClass="loic\ContentBundle\Entity\ContentRepository")
 * @ORM\Table(name="content", uniqueConstraints={@ORM\UniqueConstraint(name="idcontent_UNIQUE", columns={"idcontent"})}, indexes={@ORM\Index(name="user_id", columns={"user_id"}), @ORM\Index(name="fk_content_content_category1_idx", columns={"content_category_idcontent_category"})})
 */
class Content
{
    /**
     * @var integer
     *
     * @ORM\Column(name="idcontent", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $idcontent;

.............
/**
 * @var \Doctrine\Common\Collections\Collection
 *
 * @ORM\ManyToMany(targetEntity="loic\FilterBundle\Entity\Filter", inversedBy="contentcontent")
 * @ORM\JoinTable(name="content_has_filter",
 *   joinColumns={
 *     @ORM\JoinColumn(name="content_idcontent", referencedColumnName="idcontent")
 *   },
 *   inverseJoinColumns={
 *     @ORM\JoinColumn(name="filter_idfilter", referencedColumnName="idfilter")
 *   }
 * )
 */
private $filterfilter;

/**
 * Constructor
 */
public function __construct()
{
    $this->contentLinked = new \Doctrine\Common\Collections\ArrayCollection();
    $this->filterfilter = new \Doctrine\Common\Collections\ArrayCollection();
    $this->creationDate = new \DateTime();
}

.........

/**
 *
 * @return the \Doctrine\Common\Collections\Collection
 */
public function getFilterfilter() {
    return $this->filterfilter;
}

/**
 *
 * @param
 *          $filterfilter
 */
public function setFilterfilter($filterfilter) {
    $this->filterfilter = $filterfilter;
    return $this;
}

}

Entity Filter:

 use Doctrine\ORM\Mapping as ORM;

    /**
     * Filter
     *
     * * @ORM\Entity(repositoryClass="loic\FilterBundle\Entity\FilterRepository")
     * @ORM\Table(name="filter", uniqueConstraints={@ORM\UniqueConstraint(name="idfilter_UNIQUE", columns={"idfilter"})}, indexes={@ORM\Index(name="fk_filter_filter_category1_idx", columns={"filter_category_idfilter_category"})})
     */
    class Filter
    {
        /**
         * @var integer
         *
         * @ORM\Column(name="idfilter", type="integer", nullable=false)
         * @ORM\Id
         * @ORM\GeneratedValue(strategy="IDENTITY")
         */
        private $idfilter;

    ............


        /**
         * @var \Doctrine\Common\Collections\Collection
         *
         * @ORM\ManyToMany(targetEntity="loic\ContentBundle\Entity\Content", mappedBy="filterfilter")
         */
        private $contentcontent;

    .....

      /**
         * Constructor
         */
        public function __construct()
        {
            $this->contentcontent = new \Doctrine\Common\Collections\ArrayCollection();
            $this->user = new \Doctrine\Common\Collections\ArrayCollection();
            $this->status = 1;
        }

    ......

        /**
         *
         * @return the \Doctrine\Common\Collections\Collection
         */
        public function getContentcontent() {
            return $this->contentcontent;
        }

        /**
         *
         * @param
         *          $contentcontent
         */
        public function setContentcontent($contentcontent) {
            $this->contentcontent = $contentcontent;
            return $this;
        }

Answer 1

Since you want to check for 3 filters, you need to JOIN 3 times as well. It is as if you had 3 different ManyToMany relationships and only want one row, that fits 3 specific requirements - only difference is, that you join the same table. Untested, but it should work like this

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

Inner join is probably better as you only want rows, that do fulfill these requirements. Also be aware, that using too many joins is usually considered bad practice and may slow down performance. Preferred solution is usually to construct a specific view instead of multiple joins.