I have two entities, Story and Tag which are linked in a ManyToMany relationship.
I'm trying to get all stories that do not contain one or multiple tags (OR query, exclusion filter). So if a Story has the tags A, B and C, and I exclude the Tags B and E, then the story should not be shown. Neither if it had both B and E tags of course.
Cut down versions to the relevant files below.
Story Entity
<?php
declare(strict_types=1);
namespace App\Entity;
use App\Repository\StoryRepository;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
/**
* @ORM\Entity(repositoryClass=StoryRepository::class)
*/
class Story
{
/**
* @ORM\Id()
* @ORM\GeneratedValue()
* @ORM\Column(type="integer")
*/
private int $id;
/**
* @ORM\ManyToMany(targetEntity="Tag", inversedBy="stories")
*/
private Collection $tags;
public function __construct()
{
$this->tags = new ArrayCollection();
}
public function getId(): int
{
return $this->id;
}
public function getTags()
{
return $this->tags;
}
public function addTag(Tag $tag): void
{
$this->tags->add($tag);
}
/**
* Setting tags, clearing existing ones.
*/
public function setTags($tags): void
{
$this->tags->clear();
foreach ($tags as $tag) {
if (is_a($tag, Tag::class)) {
$this->tags->add($tag);
}
}
}
public function removeTag(Tag $tag): void
{
if ($this->tags->contains($tag)) {
$this->tags->removeElement($tag);
}
}
}
Tag Entity
<?php
declare(strict_types=1);
namespace App\Entity;
use App\Repository\TagRepository;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
/**
* @ORM\Entity(repositoryClass=TagRepository::class)
*/
class Tag
{
/**
* @ORM\Id()
* @ORM\GeneratedValue()
* @ORM\Column(type="integer")
*/
private int $id;
/**
* @ORM\ManyToMany(targetEntity="Story", mappedBy="tags")
*/
private Collection $stories;
public function __construct()
{
$this->stories = new ArrayCollection();
}
public function getId(): int
{
return $this->id;
}
}
So the Story entity is the owning side. I'm using query builder to query for the stories in the StoryRepository class.
There is a working include feature, but that has a slightly different logic, there all Tags given must be present to display the story. I'm accomplishing that with this code piece, where $includeTags is an array of Tag entities:
$qb = $this->createQueryBuilder('s');
$qb->leftJoin('s.tags', 't');
$qb->join('s.tags', 'it', Expr\Join::WITH, 'it IN (:includeTags)');
$qb->setParameter('includeTags', $includeTags);
Now for excluding I naively tried something like this:
$qb->leftJoin('s.tags', 'et', Expr\Join::ON, 'et NOT IN (:excludeTags)');
$qb->setParameter('excludeTags', $excludeTags);
Which results in Error: Expected end of string, got 'ON'
The DQL query Exception in Symfony shows me the following DQL output: SELECT s FROM App\Entity\Story s LEFT JOIN s.tags t LEFT JOIN s.tags et ON et NOT IN (:excludeTags)
Another attempt was with
$qb->andWhere($qb->expr()->notIn('s.tags', array_map(function($tag) { return $tag->getId(); }, $excludeTags)));
Which returns [Semantical Error] line 0, col X near 'tags NOT IN(1)': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.
Feeding in the entities directly gives an error on the expression handling, cutting it down to barebones like this:
$qb->andWhere('s.tags NOT IN (:excludeTags)');
$qb->setParameter('excludeTags', $excludeTags);
Brings the same Semantical error as above, just with (:excludeTags) instead of (1).
I'm pretty certain I'm missing something obvious but I can't find any answers searching for terms in general with excluding lists or array of entities (or even just array of ids) from another entity that is ManyToMany linked in Doctrine like this. Also searched specifically for Tags, as I was certain someone else must have done this before already, but I found none that would fit even close to what I need here.
Pressed, I could do it in just SQL and I know I could exclude those relatively easy, but I can't figure out how to do it "the Doctrine Way" (tm) to still be able to profit from the query builder and all the other goodies. Any help is much appreciated.
Update: I did notice I changed a line. So I tried with the following line and of course the parameter.
$qb->leftJoin('s.tags', 'et', Expr\Join::WITH, 'et NOT IN (:excludeTags)');
This now leads to the query running just fine, it just doesn't do anything, it still shows stories that should be filtered out. Getting the SQL out that is generated from that I can clearly see why:
... LEFT JOIN story_tag s5_ ON s0_.id = s5_.story_id LEFT JOIN tag t4_ ON t4_.id = s5_.tag_id AND (t4_.id NOT IN (?)) ...
Input highly appreciated.
Update 2
This query with subquery in pure sql returns the correct data as expected:
SELECT id FROM story WHERE id NOT IN (SELECT story_id FROM story_tag WHERE tag_id IN (1))
I'd very much like to avoid having to do a subquery as I was able to do with the include tags query, but if not possible then I'll just bite the bullet on that one.