phpmysqldoctrine-ormdoctrine

Symfony join table ignored id affects other data


I want to do a left join from "page" to "terms" using NOT IN

Entities

/**
 * @ORM\Entity(repositoryClass="AppBundle\Repository\PageRepository")
 * @ExclusionPolicy("all")
 */
class Page
{
    /**
     * @ORM\ManyToMany(targetEntity="Term", inversedBy="pages")
     */
    protected $terms;

    public function __construct()
    {
        $this->terms = new ArrayCollection();
    }

    /**
     * @return mixed
     */
    public function getTerms()
    {
        return $this->terms;
    }

    /**
     * @param mixed $terms
     */
    public function setTerms($terms)
    {
        $this->terms = $terms;
    }
}



/**
 * @ORM\Entity(repositoryClass="AppBundle\Repository\TermRepository")
 * @ExclusionPolicy("all")
 */
class Term
{
    /**
     * @ORM\ManyToMany(targetEntity="Page", mappedBy="terms", cascade={"persist"}, fetch="LAZY"))
     */
    protected $pages;

    public function __construct()
    {
        $this->pages = new ArrayCollection();
    }

    public function getPages()
    {
        return $this->pages;
    }

    public function setPages($pages)
    {
        $this->pages = $pages;
    }
}

$ignoreTerms array

$ignoreTerms = [
    $this->getParameter('param1'),
    $this->getParameter('param2'),
    $this->getParameter('param3'),
    $this->getParameter('param4'),
];

PageRepository

$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select('p')
    ->from('AppBundle:Page', 'p')
    ->leftJoin('p.terms','tt'); // probably wrong use of join here

if(!empty($limit))
    $qb->setMaxResults($limit);

if (!empty($ignoreTerms)) {
    $qb->andWhere(
        $qb->expr()->notIn('tt.id', $ignoreTerms)
    );
}

return $qb;

SQL generated by the query builder (after removing other conditions not necessary in the question)

SELECT p0_.* 
FROM page p0_ LEFT JOIN page_term p2_ ON p0_.id = p2_.page_id 
LEFT JOIN term t1_ ON t1_.id = p2_.term_id 
WHERE t1_.id NOT IN (--the array here--) 
ORDER BY p0_.`publishDate` 
DESC LIMIT 15 OFFSET 0

When running the query builder, it does ignore pages with terms from array. However, pages without terms are also ignored.

What I expect it to do is to also fetch pages with or without terms

The join methods available are

  1. join()
  2. innerJoin()
  3. leftJoin()

Tried all but there are no change


Solution

  • To get all pages except ignoredTerms need to use OR.

    Final query

    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb->select('p')
        ->from('AppBundle:Page', 'p')
        ->leftJoin('p.terms','tt'); // probably wrong use of join here
        ->where($qb->expr()->orX(
           $qb->expr()->isNull('tt.id'), // Include pages with no terms
           $qb->expr()->notIn('tt.id', ':ignoreTerms') // Exclude specific terms
       ))
       ->setParameter('ignoreTerms', $ignoreTerms);
    
    if(!empty($limit))
        $qb->setMaxResults($limit);
    
    return $qb;