Hi Django users.
My first time mailing this list, so apologies in advance if i don't follow
protocol 100%.
Please see the sample use case below.
Basically, by ordering the Q statements in a different order, I get a
different SQL statement and different results. I can see a predicate has
been pushed down into a subquery on the first example and this is the
cause, but can't work out where in the code this is happening and why.
There are better ways of trying to get the answer this query is looking
for, but this is a massive simplification of a more complex scenario where
this is necessary...
Thanks in advance everyone
Chris
class Book(BaseModel):
name = models.CharField(max_length=CHAR_MAX_LEN, null=True, blank=True)
class BookStatus(BaseModel):
book = models.ForeignKey(Book, null=False, blank=False,
on_delete=models.CASCADE, related_name='statuses')
status = models.CharField(max_length=CHAR_MAX_LEN, null=True,
blank=True)
valid_from = models.DateTimeField(db_index=True)
valid_to = models.DateTimeField(db_index=True, blank=True, null=True,
default=None)
from django.utils import timezone
from app.models import *
from django.db.models import Q, Subquery
b1 = Book.objects.create(name='Green is the new Blue')
b2 = Book.objects.create(name='Orange is the new Red')
BookStatus.objects.create(book=b1, status='DRAFT',
valid_from=timezone.now())
BookStatus.objects.create(book=b1, status='PUBLISHED',
valid_from=timezone.now())
BookStatus.objects.create(book=b2, status='DRAFT',
valid_from=timezone.now())
q1 =
Q(statuses__id__in=Subquery(BookStatus.objects.only('id').filter(status='DRAFT')))
q2 =
~Q(statuses__id__in=Subquery(BookStatus.objects.only('id').filter(status='PUBLISHED')))
qs = Book.objects.only('name').filter(q1 & q2) # NOTICE Q1 BEFORE Q2
str(qs.query)
'SELECT "utils_book"."id", "utils_book"."name" FROM "utils_book" INNER JOIN
"utils_bookstatus" ON ("utils_book"."id" = "utils_bookstatus"."book_id")
WHERE ("utils_bookstatus"."id" IN (SELECT U0."id" FROM "utils_bookstatus"
U0 WHERE U0."status" = DRAFT) AND NOT ("utils_book"."id" IN (SELECT
V1."book_id" FROM "utils_bookstatus" V1 WHERE (V1."id" IN (SELECT U0."id"
FROM "utils_bookstatus" U0 WHERE U0."status" = PUBLISHED) AND V1."id" =
("utils_bookstatus"."id")))))
qs
<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>]>
qs = Book.objects.only('name').filter(q2 & q1) # NOTICE Q2 BEFORE Q1
str(qs.query)
'SELECT "utils_book"."id", "utils_book"."name" FROM "utils_book" INNER JOIN
"utils_bookstatus" ON ("utils_book"."id" = "utils_bookstatus"."book_id")
WHERE (NOT ("utils_book"."id" IN (SELECT V1."book_id" FROM
"utils_bookstatus" V1 WHERE V1."id" IN (SELECT U0."id" FROM
"utils_bookstatus" U0 WHERE U0."status" = PUBLISHED))) AND
"utils_bookstatus"."id" IN (SELECT U0."id" FROM "utils_bookstatus" U0 WHERE
U0."status" = DRAFT))'
qs
<QuerySet [<Book: Book object (2)>]>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/67dc361e-a601-43d9-96c4-f894efd845c8n%40googlegroups.com.
