alias (optional) which I need to search for. So, I need a query to give me all the names that have an alias set.
Only if I could do:
So, what is the equivalent to the above?
You could do this:
If you need to exclude null values and empty strings, the preferred way to do so is to chain together the conditions like so:
Chaining these methods together basically checks each condition independently: in the above example, we exclude rows where
alias is either null or an empty string, so you get all
Name objects that have a not-null, not-empty
alias field. The generated SQL would look something like:
SELECT * FROM Name WHERE alias IS NOT NULL AND alias != ""
You can also pass multiple arguments to a single call to
exclude, which would ensure that only objects that meet every condition get excluded:
Here, rows in which
other_field are true get excluded, so we get all rows where both fields are not true. The generated SQL code would look a little like this:
SELECT * FROM Name WHERE NOT (some_field = TRUE AND other_field = TRUE)
Alternatively, if your logic is more complex than that, you could use Django’s Q objects:
from django.db.models import Q Name.objects.exclude(Q(alias__isnull=True) | Q(alias__exact=""))
As an aside: My SQL examples are just an analogy–the actual generated SQL code will probably look different. You’ll get a deeper understanding of how Django queries work by actually looking at the SQL they generate.
Firstly, the Django docs strongly recommend not using NULL values for string-based fields such as CharField or TextField. Read the documentation for the explanation:
You can also chain together methods on QuerySets, I think. Try this:
That should give you the set you’re looking for.
From Django 1.8,
from django.db.models.functions import Length Name.objects.annotate(alias_length=Length('alias')).filter(alias_length__gt=0)
1. When using exclude, keep the following in mind to avoid common mistakes:
Should not add multiple conditions into an
exclude() block like
filter(). To exclude multiple conditions, you should use multiple
(NOT a AND NOT b)
SELECT... WHERE NOT title="" AND NOT headline=""
2. Only use multiple when you really know about it:
NOT (a AND b)
SELECT.. WHERE NOT (title="" AND headline="")
You can simply do this:
It’s really just that simple.
filter is used to match and
exclude is to match everything but what it specifies. This would evaluate into SQL as
NOT alias="" AND alias IS NOT NULL.
this is another simple way to do it .
Another approach using a generic
isempty lookup, that can be used with any field.
It can also be used by django rest_framework or other apps that use django lookups:
from distutils.util import strtobool from django.db.models import Field from django.db.models.lookups import BuiltinLookup @Field.register_lookup class IsEmpty(BuiltinLookup): lookup_name="isempty" prepare_rhs = False def as_sql(self, compiler, connection): sql, params = compiler.compile(self.lhs) condition = self.rhs if isinstance(self.rhs, bool) else bool(strtobool(self.rhs)) if condition: return "%s IS NULL or %s=""" % (sql, sql), params else: return "%s <> ''" % sql, params
You can then use it like this: