CODEWITHSUNDEEP
phpmysqldoctrinesymfony4
serj
serj

Reputation: 189

Symfony 4 query elements from joined table if they exist

I have two tables gifts and gift_tags how can I select all gifts with tags that are not present in $avoidTags array only if gift has tags. The way my query works now it selects only the gifts that have at least one tag in gift_tags table and it is not in $avoidTags array, but I need also those that have no tags at all.

$response = $qb
            ->select('g, tags')
            ->leftJoin('g.tags', 'tags')
            ->andwhere($qb->expr()->notin("tags.name", ":avoidTags"))
            ->setParameters(new ArrayCollection([
                new Parameter('avoidTags', $avoidTags),
            ]))
            ->getQuery()
            ->getResult();

Upvotes: 1

Views: 101

Answers (1)

Ihor Kostrov
Ihor Kostrov

Reputation: 2561

With the mysql, it can do with this query

SELECT 
  gift.*, tag.* 
FROM 
  gift 
LEFT JOIN tag ON gift.id = tag.gift_id
  WHERE gift.id NOT IN (
    SELECT g.id FROM gift AS g LEFT JOIN tag AS t ON g.id = t.gift_id WHERE t.name IN ('test', 'test2')
  )

So with the doctrine QB it maybe something like this 

 $subQb = $this->_em->createQueryBuilder()
            ->select('gift.id')
            ->leftJoin('gift.tags', 'tags')
            ->where($qb->expr()->orX(
                $qb->expr()->in("tags.name", ":avoidTags")),
            ))
            ->getQuery();

$response = $qb
        ->select('g, tags')
        ->leftJoin('g.tags', 't')
        ->where($qb->expr()->notin("g.id", $subQb->getDql())))
        ->setParameters(new ArrayCollection([
            new Parameter('avoidTags', $avoidTags),
        ]))
        ->getQuery()
        ->getResult();

Upvotes: 1

Related Questions