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.