Query Q() Is Not Working As 'set Contain' For Related Field In Django?

Let's say we have baskets of fruits. How to filter out those baskets that contains all fruits in a given basket? In this document https://docs.djangoproject.com/en/dev/ref/models/

Solution 1:

Not sure if this is the most performant way to go, but at least it should translate to one (big, ugly, nested) sql transaction (once the final baskets queryset is evaluated):

baskets = Basket.objects.all()
for fruit in fruits:
    baskets = baskets.filter(id__in=fruit.basket.all())

A more elegant (and possibly more performant) way could be tried as follows composing a query using Q objects (building on Dave Webb's answer to another question):

queries = [Q(id__in=fruit.basket.all()) for fruit in fruits]

query = Q()

# AND the Q object with the ones in the list
for item in queries:
    query &= item

baskets = Basket.objects.filter(query)

Solution 2:

By printing the raw SQL of the Q & operations, I've found the reason why Q is working as this.

from django.db import connection
print connection.queries
u'SELECT DISTINCT "market_basket"."id", "market_basket"."weight" FROM "market_basket" INNER JOIN "market_fruit"ON ("market_basket"."id" = "market_fruit"."inbasket_id") INNER JOIN "market_fruitname"ON ("market_fruit"."ofkind_id" = "market_fruitname"."id") WHERE ("market_fruitname"."name"IN (\'apple\') AND "market_fruitname"."name"IN (\'banana\')) LIMIT 21'

the key problem is WHERE clause will not be satisfied in a single condition when queries is used in a single filter. It is actually looking for a fruitname is both in ['apple'] and ['banana'] which is not possible. What is needed is to find (those fruits that have fruitname of 'apple') or (those fruits that have fruitname of 'banana')

Currently the only viable solution is to chain filters.

Solution 3:

To get a Basket with all available Fruit instances in it, you can do something like this:

from django.db.models import Count

# firstgetall PKs of fruits
fruit_pk_list = Fruit.objects.value_list('id', flat=True)

# Thengetfilter the basket withall fruits using annotate and Count
baskets = Basket.objects.annotate(
    num_fruit=Count('fruit')).filter(num_fruit=len(fruit_pk_list))