django reverse foreign key pefetch related, queryset condition not working

Question

Consider the following condition:

class Book(models.Model):
    name = models.CharField(max_length=300)
    price = models.IntegerField(default=0)

    def __str__(self):
        return self.name


class Store(models.Model):
    name = models.CharField(max_length=300)
    default = models.BooleanField(default=False)
    books = models.ForeignKey(Book, on_delete=models.CASCADE)

So, for this query:

Book.objects.prefetch_related(Prefetch('store_set',
            queryset=Store.objects.filter(default=False)))
            .values("store__name", "name", "store__default")

The SQL query is not considering queryset default=True condition

SELECT "core_store"."name",
       "core_book"."name",
       "core_store"."default"
  FROM "core_book"
  LEFT OUTER JOIN "core_store"
    ON ("core_book"."id" = "core_store"."books_id")

Result:

<QuerySet [{'store__name': 'Subway Store', 'name': 'Hello', 'store__default': False}, 
{'store__name': 'Times Square', 'name': 'Hello', 'store__default': False}, 
{'store__name': 'Subway Store', 'name': 'GoodBye', 'store__default': True}, 
{'store__name': 'Times Square', 'name': 'GoodBye', 'store__default': False}, 
{'store__name': 'Subway Store', 'name': 'Greetings', 'store__default': True}, 
{'store__name': 'Subway Store', 'name': 'HateWords', 'store__default': False}]>

I want to have a query set condition while prefetching the query. I am not able to find any way to do it in one query or a minimum number of queries.

I was thinking it should make a where condition with the OUTER JOIN with core_store table. Here

LEFT OUTER JOIN "core_store"
    ON ("core_book"."id" = "core_store"."books_id")

Do you want only the name field from `Book` instances that are related to a `Store` instance? — Lax_Sam, Feb 12 '23 at 07:53
No, everything is required as stated above. ```.values("store__name", "name", "store__default")``` — Nishant Kashyap, Feb 12 '23 at 14:52
According to the above code, u are querying only `name` from `Book` and remaining 2 fields are from `Store` model. — Lax_Sam, Feb 12 '23 at 16:08

score 0 · Answer 1 · answered Feb 12 '23 at 16:12

If you want only the values as stated in your question then you can directly use fk relationship from the Store model to Book model as below. No need to do reverse lookup.

Store.objects.filter(default=True).values('name', 'default', 'books__name')

But if u want to fetch all Books and also their corresponding Store then you can use only on prefetch_related. .values does not work with prefetch_related as stated in the docs. You can also use a for loop after assigning a to_attr in your prefetch_related query. The for loop does not do a db query. Instead it gets the value from prefetched results.

books_stores = Book.objects.prefetch_related(Prefetch('store_set',
            queryset=Store.objects.filter(default=False), 
            to_attr='stores'))


for book_store in book_stores:
    stores = book_store.stores

unfortunately , this is not what I am looking for, but thanks. This is achievable. — Nishant Kashyap, Feb 13 '23 at 09:21
`.values` does not work with `prefetch_related`. if you are looking for that. — Lax_Sam, Feb 13 '23 at 09:29

django reverse foreign key pefetch related, queryset condition not working

1 Answers1