Django In/Not in Query

Django in / not in query

table1.objects.exclude(id__in=
    table2.objects.filter(your_condition).values_list('id', flat=True))

The exclude function works like the Not operator you where asking for. The attribute flat = True tells to table2 query to return the value_list as a one level list. So... at the end you are obtaining a list of IDs from table2, which you are going to user to define the condition in table1, that will be denied by the exclude function.

How do I do a not equal in Django queryset filtering?

You can use Q objects for this. They can be negated with the ~ operator and combined much like normal Python expressions:

from myapp.models import Entry
from django.db.models import Q

Entry.objects.filter(~Q(id=3))

will return all entries except the one(s) with 3 as their ID:

[<Entry: Entry object>, <Entry: Entry object>, <Entry: Entry object>, ...]

Django equivalent of SQL not in

try using exclude

Table.objects.exclude(title__in=myListOfTitles)

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 LookupIn

class NotIn(LookupIn):
    lookup_name = "notin"

    def get_rhs_op(self, connection, rhs):
        return "NOT IN %s" % rhs

Field.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)))

In a Django QuerySet, how to filter for not exists in a many-to-one relationship

Note: this answer was written in 2013 for Django 1.5. See the other answers for better approaches that work with newer versions of Django

Use isnull.

users_without_reports = User.objects.filter(report__isnull=True)
users_with_reports = User.objects.filter(report__isnull=False).distinct()

When you use isnull=False, the distinct() is required to prevent duplicate results.

Django ORM query performance

You have not considered the fact that QuerySets are lazy, that is when you create a queryset no actual query is made to the database. The query will run only when the results of the query are actually needed.

So when you write Model.objects.filter(...) all that does is create a query. You should instead force the evaluation of the queryset, for example by converting it to a list. This will actually run the query and give you more accurate results:

list(Model.objects.filter(...))

Django query where in

Two querysets is documented way of doing this. It will be one database hit anyway.

test_ids = Subject.objects.all()
result = Test.objects.filter(test_id__in=test_ids).filter([some other filtering])

django: how to make query be not lazy executed?

The problem is not the laziness, the problem is that you make a QuerySet per PRODUCT_STATUS.

You can make such dictionary with a single pass with the groupby function [Python-doc] of the itertools module [Python-doc]:

from itertools import groupby
from operator import attrgetter

class ExtendedManager(models.Manager):
    def separated_by_status(self, product_type):
        query = super().get_queryset().order_by('status')
        dict_ = {
            k: list(vs)
            for k, vs in groupby(query, attrgetter('status'))
        }
        return {
            status1: dict_.get(status0, [])
            for status0, status1 in PRODUCT_STATUS
        }

---

Related Topics