"&" and "|" vs "and" and "or" for "AND" and "OR" operators in Django
I have Blog
model below. *I use Django 3.2.16 and PostgreSQL:
# "store/models.py"
from django.db import models
class Blog(models.Model):
post = models.TextField()
def __str__(self):
return self.post
Then, store_blog
table has 2 rows below:
store_blog
table:
id | post |
---|---|
1 | Python is popular and simple. |
2 | Java is popular and complex. |
Then, when running the filter() code using &
or Q() and &
or using and
or Q()
and and
in test()
view as shown below:
# "store/views.py"
from .models import Blog
from django.db.models import Q
def test(request):
# With "&"
# ↓ Here
qs = Blog.objects.filter(post__contains="popular") & \
Blog.objects.filter(post__contains="simple")
print(qs)
# With "Q()" and "&"
# ↓ Here # ↓ Here
qs = Blog.objects.filter(Q(post__contains="popular") &
Q(post__contains="simple"))
print(qs) # ↑ Here
# With "and"
# ↓ Here
qs = Blog.objects.filter(post__contains="popular") and \
Blog.objects.filter(post__contains="simple")
print(qs)
# With "Q()" and "and"
# ↓ Here # ↓ Here
qs = Blog.objects.filter(Q(post__contains="popular") and
Q(post__contains="simple"))
print(qs) # ↑ Here
return HttpResponse("Test")
I got the same result below:
<QuerySet [<Blog: Python is popular and simple.>]> # With "&"
<QuerySet [<Blog: Python is popular and simple.>]> # With "Q()" and "&"
<QuerySet [<Blog: Python is popular and simple.>]> # With "and"
<QuerySet [<Blog: Python is popular and simple.>]> # With "Q()" and "and"
[22/Dec/2022 16:04:45] "GET /store/test/ HTTP/1.1" 200 9
And, when running the filter()
code using |
or Q()
and |
or using or
or Q()
and or
in test()
view as shown below:
# "store/views.py"
from .models import Blog
from django.db.models import Q
def test(request):
# With "|"
# ↓ Here
qs = Blog.objects.filter(post__contains="popular") | \
Blog.objects.filter(post__contains="simple")
print(qs)
# With "Q()" and "|"
# ↓ Here # ↓ Here
qs = Blog.objects.filter(Q(post__contains="popular") |
Q(post__contains="simple"))
print(qs) # ↑ Here
# With "or"
# ↓ Here
qs = Blog.objects.filter(post__contains="popular") or \
Blog.objects.filter(post__contains="simple")
print(qs)
# With "Q()" and "or"
# ↓ Here # ↓ Here
qs = Blog.objects.filter(Q(post__contains="popular") or
Q(post__contains="simple"))
print(qs) # ↑ Here
return HttpResponse("Test")
I got the same result below:
<QuerySet [<Blog: Python is popular and simple.>, <Blog: Java is popular and complex.>]> # With "|"
<QuerySet [<Blog: Python is popular and simple.>, <Blog: Java is popular and complex.>]> # With "Q()" and "|"
<QuerySet [<Blog: Python is popular and simple.>, <Blog: Java is popular and complex.>]> # With "or"
<QuerySet [<Blog: Python is popular and simple.>, <Blog: Java is popular and complex.>]> # With "Q()" and "or"
[22/Dec/2022 16:20:27] "GET /store/test/ HTTP/1.1" 200 9
So, are there any differences between &
and and
and |
and or
for AND
and OR
operators in Django?
Your test data is quite insufficient for this test since both rows contain popular
. (IOW, anything that would find popular
is equivalent to Blog.objects.all()
.)
Anyway, boolean or
and and
don't work correctly here (since they're just doing boolean evaluation), but you're just not noticing because of the above.
You need to use the "bitwise" |
/ &
/ ^
operators, as documented. ("bitwise" in quotes, since the Q
class overloads the operators to not be bitwise at all.)
For proof, let's look at the Q
s in isolation, without querying anything...
>>> from django.db.models import Q
>>> pop = Q(post__contains="popular")
<Q: (AND: ('post__contains', 'popular'))>
>>> sim = Q(post__contains="simple")
<Q: (AND: ('post__contains', 'simple'))>
>>> pop and sim
<Q: (AND: ('post__contains', 'simple'))>
# ^ incorrect; evaluates to `sim`
>>> pop & sim
<Q: (AND: ('post__contains', 'popular'), ('post__contains', 'simple'))>
# ^ correct: combines the two q:s with `and`
>>> pop or sim
<Q: (AND: ('post__contains', 'popular'))>
# ^ incorrect; evaluates to `pop`
>>> pop | sim
<Q: (OR: ('post__contains', 'popular'), ('post__contains', 'simple'))>
# ^ correct: combines the two q:s with `or`
When running the filter()
code using &
or Q()
and &
or using and
or Q()
and and
in test()
view as shown below:
# "store/views.py"
from .models import Blog
from django.db.models import Q
from django.http import HttpResponse
def test(request):
# With "&"
# ↓ Here
qs = Blog.objects.filter(post__contains="popular") & \
Blog.objects.filter(post__contains="simple")
print(qs)
# With "Q()" and "&"
# ↓ Here # ↓ Here
qs = Blog.objects.filter(Q(post__contains="popular") &
Q(post__contains="simple"))
print(qs) # ↑ Here
# With "and"
# ↓ Here
qs = Blog.objects.filter(post__contains="popular") and \
Blog.objects.filter(post__contains="simple")
print(qs)
# With "Q()" and "and"
# ↓ Here # ↓ Here
qs = Blog.objects.filter(Q(post__contains="popular") and
Q(post__contains="simple"))
print(qs) # ↑ Here
return HttpResponse("Test")
&
or Q()
and &
can run AND
operators according to the query logs of PostgreSQL as shown below. *You can check on PostgreSQL, how to log queries with transaction queries such as "BEGIN" and "COMMIT":
And, when running the filter()
code using |
or Q()
and |
or using or
or Q()
and or
in test()
view as shown below:
# "store/views.py"
from .models import Blog
from django.db.models import Q
from django.http import HttpResponse
def test(request):
# With "|"
# ↓ Here
qs = Blog.objects.filter(post__contains="popular") | \
Blog.objects.filter(post__contains="simple")
print(qs)
# With "Q()" and "|"
# ↓ Here # ↓ Here
qs = Blog.objects.filter(Q(post__contains="popular") |
Q(post__contains="simple"))
print(qs) # ↑ Here
# With "or"
# ↓ Here
qs = Blog.objects.filter(post__contains="popular") or \
Blog.objects.filter(post__contains="simple")
print(qs)
# With "Q()" and "or"
# ↓ Here # ↓ Here
qs = Blog.objects.filter(Q(post__contains="popular") or
Q(post__contains="simple"))
print(qs) # ↑ Here
return HttpResponse("Test")
|
or Q()
and |
can run OR
operators according to the query logs of PostgreSQL as shown below: