Filtering for empty or NULL names in a queryset
I have first_name, last_name & 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:
Name.objects.filter(alias!="")
So, what is the equivalent to the above?
You could do this:
Name.objects.exclude(alias__isnull=True)
If you need to exclude null values and empty strings, the preferred way to do so is to chain together the conditions like so:
Name.objects.exclude(alias__isnull=True).exclude(alias__exact='')
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:
Name.objects.exclude(some_field=True, other_field=True)
Here, rows in which some_field and 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=''))
For more info see this page and this page in the Django docs.
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:
https://docs.djangoproject.com/en/dev/ref/models/fields/#null
Solution: You can also chain together methods on QuerySets, I think. Try this:
Name.objects.exclude(alias__isnull=True).exclude(alias="")
That should give you the set you're looking for.
Name.objects.filter(alias__gt='',alias__isnull=False)
上一篇: 如何在django中使用排除参数传递queryset
下一篇: 筛选查询集中的空名称或空名称