Django ORM: Equivalent of SQL `NOT IN`? `exclude` and `Q` objects do not work

I think the easiest way to do this would be to define a custom lookup, similar to this one or the in lookup

from django.db.models.lookups import In as LookupInclass NotIn(LookupIn):    lookup_name = "notin"    def get_rhs_op(self, connection, rhs):        return "NOT IN %s" % rhsField.register_lookup(NotIn)

or

class NotIn(models.Lookup):    lookup_name = "notin"    def as_sql(self, compiler, connection):        lhs, params = self.process_lhs(compiler, connection)        rhs, rhs_params = self.process_rhs(compiler, connection)        params.extend(rhs_params)        return "%s NOT IN %s" % (lhs, rhs), params

then use it in your query:

query = (    JobLog.objects.values(        "username", "job_number", "name", "time",    )    .filter(time__gte=start, time__lte=end, event="delivered")    .filter(        job_number__notin=models.Subquery(            JobLog.objects.values_list("job_number", flat=True).filter(                time__gte=start, time__lte=end, event="finished",            )        )    ))

this generates the SQL:

SELECT    "people_joblog"."username",    "people_joblog"."job_number",    "people_joblog"."name",    "people_joblog"."time"FROM    "people_joblog"WHERE ("people_joblog"."event" = delivered    AND "people_joblog"."time" >= 2020 - 03 - 13 15:24:34.691222 + 00:00    AND "people_joblog"."time" <= 2020 - 03 - 13 15:24:41.678069 + 00:00    AND "people_joblog"."job_number" NOT IN (        SELECT            U0. "job_number"        FROM            "people_joblog" U0        WHERE (U0. "event" = finished            AND U0. "time" >= 2020 - 03 - 13 15:24:34.691222 + 00:00            AND U0. "time" <= 2020 - 03 - 13 15:24:41.678069 + 00:00)))

You can likely achieve the same results by using an Exists and special casing NULLs.

.filter(   ~Exists(       JobLog.objects.filter(           Q(jobnumber=None) | Q(jobnumber=OuterRef('jobnumber')),           time__gte=start,           time__lte=end,           event='finished',       )   ))

Can you try this:

JobLog.objects.filter(time__gte=start, time__lte=end, event="delivered").exclude(time__gte=start, event='finished').exclude(time__lte=end, event='finished')