How to select rows which have both items in ManyToMany relation

user_10788387 0
Viewed 54

Let's assume i have "News" entity which has got ManyToMany "Tag" relation

class News
{
    /**
     * @ORM\ManyToMany(targetEntity="App\Domain\Entity\Vocabulary\Tag")
     */
    private Collection $tags;
}

And i have such query:

public function getList(
    array $tags = null,
): Query {
    if (null !== $tags) {
        $qb->andWhere('nt.id IN (:tags)');
        $qb->setParameter('tags', $tags);
    }
}

The problem is when i pass ["Tag1", "Tag2"] it selects news that have either the first tag or the second, but not both at the same time. How can i rewrite the query to select news which have both tags at the same time?

query-builder doctrine-orm dql doctrine
1 Answers

Some things to notice first:

For doctrine annotations it is possible to use the ::class-constant:

use App\Domain\Entity\Vocabulary\Tag;

class News
{
    /**
     * @ORM\ManyToMany(targetEntity=Tag::class)
     */
    private Collection $tags;
 }

If the $tags array is empty doctrine will throw an exception because an empty value set is invalid SQL, at least in mysql:

nt.id IN () # invalid!

Now to the problem:

With the SQL-aggregation functions COUNT and GROUP BY we can count the number of tags for all news. Together with your condition for the allowed tags, the number of tags per news must be equal to the number of tags in the tags array:

/**
 * @var EntityManagerInterface
 */
private $manager;

...

/**
 * @param list<Tag> $tags - Optional tag filter // "list" is a vimeo psalm annotation.
 *
 * @return list<News>
 */
 public function getNews(array $tags = []): array 
 {
    $qb = $this->manager
        ->createQueryBuilder()
        ->from(News::class, 'news')
        ->select('news')
    ;

    if(!empty($tags)) {
        $tagIds = array_unique(
            array_map(static function(Tag $tag): int {
                return $tag->getId();
            }) // For performance reasons, give doctrine ids instead of objects.
        ); // Make sure duplicate tags are handled.

        $qb
            ->join('news.tags', 'tag')
            ->where('tag IN (:tags)') 
            ->setParameter('tags', $tagIds) 
            ->addSelect('COUNT(tag) AS HIDDEN numberOfTags') 
            ->groupBy('news') 
            ->having('numberOfTags = :numberOfTags') 
            ->setParameter('numberOfTags', count($tags)) 
        ;
    }

    return $qb
        ->getQuery()
        ->getResult()
    ;
}
user_52331880
Related