How to improve/rewrite this query in Doctrine Query Builder (NOT IN with subquery)

Question

I have a rather simple raw SQL query which works as I want:

SELECT * FROM set 
WHERE set.is_finalized = 1 
AND set.id NOT IN 
(SELECT set_completion.set_id FROM set_completion WHERE set_completion.user_id = 2)

Now I try to get the same result set in Doctrine Query Builder. I built something like this, which also works and gives me the correct result:

$subQuery = $this->_em->createQueryBuilder();
$subQueryResult = $subQuery
            ->select('IDENTITY(c.set)')
            ->from('App\Entity\SetCompletion', 'c')
            ->where('c.user = :user')
            ->setParameter('user', $user)
            ->getQuery()
            ->getArrayResult()
        ;
        
return $this->createQueryBuilder('s')
            ->andWhere('s.isFinalized = :finalized')
            ->andWhere('s.id not in (:completions)')
            ->setParameter('finalized', true)
            ->setParameter('completions', $subQueryResult)
            ->orderBy('s.id', 'DESC')
            ->getQuery()
            ->getResult()
        ;

However I do feel that this looks overcomplicated and not very clean. For the sake of learning, is there a way to achieve the same result with something less bloated? Perhaps something utilizing JOIN-s in a single query? Sometimes I simply can't wrap my head around joining results in DQL/Builder when it comes to something more than simple inclusive conditions.

Answer 1

Convert your "rather simple raw SQL query which works as I want" to

SELECT set.* 
FROM set 
LEFT JOIN set_completion ON set.id = set_completion.set_id
                        AND set_completion.user_id = 2
WHERE set.is_finalized = 1 
  AND set_completion.set_id IS NULL

Then build DQL representation of this query.