Convert NOT IN (SELECT ...) from SQL to DQL

Question

I've got a query which works in SQL :
SELECT * FROM SOCIETY WHERE id NOT IN (SELECT society_id FROM activity)
I'm trying to convert this into a DQL so I tried like this :

return $this->createQueryBuilder('s')
            ->select('s')
            ->from(Society::class, 's')
            ->andWhere('s.id NOT IN (SELECT societyId FROM activity)')
            ;

But I got this error: [Semantical Error] line 0, col 103 near 'activity)': Error: Class 'activity' is not defined.

So I edited my request like this :

return $this->createQueryBuilder('s')
            ->select('s')
            ->from(Society::class, 's')
            ->leftJoin('s.activity', 'a')
            ->andWhere('s.id NOT IN (SELECT societyId FROM :activity)')
            ->setParameter('activity', 'activity')
            ;

But still got errors.
Any ideas ?

Answer 1

Your SQL can be written using a join instead of a sub query

SELECT s.* FROM SOCIETY s
LEFT JOIN activity a ON s.id = a.society_id
WHERE a.id IS NULL

In query builder you can write it as

$this->createQueryBuilder('s')
    ->select('s')
    ->from(Society::class, 's')
    ->leftJoin('s.activity', 'a')
    ->where('a.id IS NULL')
    ;