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
join()
innerJoin()
leftJoin()
Tried all but there are no change
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;