Q() chaining with & returns empty set

[Bai Shun]

I've got a problem in using Q()

http://stackoverflow.com/questions/36299975/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/querysets/#in 'in' method seems will return any basket which contains any of the given fruit. Is there any "set_contains" method to be used for filtering ? Such as Basket.objects.filter(fruit_set_containsAll=fruitSet) ?

Edit: I found that Q() is not working as expected. I'll post the test here:

class Basket(models.Model):
    weight = models.FloatField(default=1)
class Fruitname(models.Model):
    name = models.CharField(max_length=32)
class Fruit(models.Model):
    ofkind = models.ForeignKey(Fruitname, on_delete=models.CASCADE)  
    inbasket = models.ForeignKey(Basket, on_delete=models.CASCADE)  
    weight = models.FloatField(default=1)

the database is set as 'apple' only in 1 basket, 'pear' in 2 baskets, and 'banana' in 3 basket:

print Basket.objects.all()
print Fruitname.objects.all()
[<Basket: id 31 has 4 fruits 
    1. id - 53 apple(28), weight 1.00
    2. id - 54 apple(28), weight 2.00
    3. id - 55 apple(28), weight 3.00
    4. id - 62 banana(30), weight 10.00
>, <Basket: id 32 has 2 fruits 
    1. id - 56 pear(29), weight 4.00
    2. id - 57 banana(30), weight 5.00
>, <Basket: id 33 has 4 fruits 
    1. id - 58 pear(29), weight 6.00
    2. id - 59 banana(30), weight 7.00
    3. id - 60 pear(29), weight 8.00
    4. id - 61 pear(29), weight 9.00
>]
[<Fruitname: apple(28)>, <Fruitname: pear(29)>, <Fruitname: banana(30)>]

If I try to query with 'apple' and 'banana', it gives empty set !!

print Basket.objects.filter(Q(fruit__ofkind__name__in=['apple'])&Q(fruit__ofkind__name__in=['banana'])).distinct()
[]

similarly,

print Basket.objects.filter(Q(fruit__ofkind__name__in=['banana'])&Q(fruit__ofkind__name__in=['pear'])).distinct()
[]

Here's how I use 'in' to filter, it is not what I need which is supposed to be an empty set.

print Basket.objects.filter(Q(fruit__ofkind__name__in=['apple','pear'])).distinct()
[<Basket: id 31 has 4 fruits 
    1. id - 53 apple(28), weight 1.00
    2. id - 54 apple(28), weight 2.00
    3. id - 55 apple(28), weight 3.00
    4. id - 62 banana(30), weight 10.00
>, <Basket: id 32 has 2 fruits 
    1. id - 56 pear(29), weight 4.00
    2. id - 57 banana(30), weight 5.00
>, <Basket: id 33 has 4 fruits 
    1. id - 58 pear(29), weight 6.00
    2. id - 59 banana(30), weight 7.00
    3. id - 60 pear(29), weight 8.00
    4. id - 61 pear(29), weight 9.00
>]

The only way that is working properly is chaining with filter:

Basket.objects.filter(fruit__ofkind__name__in=['apple']).filter(fruit__ofkind__name__in=['banana']).distinct()
[<Basket: id 31 has 4 fruits 
    1. id - 53 apple(28), weight 1.00
    2. id - 54 apple(28), weight 2.00
    3. id - 55 apple(28), weight 3.00
    4. id - 62 banana(30), weight 10.00
>]

These code is tested with Django 1.9.4 Any explaining? I would undo the accepted answer for the moment.

[Erik Cederstrand]

> Den 31. mar. 2016 kl. 04.14 skrev Bai Shun <wany...@gmail.com>:
>
> I've got a problem in using Q()

To see what's going on, try to print the SQL that Django generates for working and non-working queryset:

> print Basket.objects.filter(Q(fruit__ofkind__name__in=['apple'])&Q(fruit__ofkind__name__in=['banana'])).distinct()

> Basket.objects.filter(fruit__ofkind__name__in=['apple']).filter(fruit__ofkind__name__in=['banana']).distinct()

Print SQL like this:

print(my_queryset.query)


Erik

[Peter of the Norse]

They talk about this in the documentation.   https://docs.djangoproject.com/en/1.8/topics/db/queries/#spanning-multi-valued-relationships  Anything in a single filter() involves the same object.  For example, if you wanted to find all baskets containing a three pound banana, you would do Basket.objects.filter(fruit__weight=3.0, fruit__ofkind__name=‘banana’).  If you split them up into different filter()s, it would find baskets that have a three pound fruit of any type and a banana of any weight.

I think this is fairly new.  I’ve written code like Basket.objects.filter(fruit__in=Fruit.objects.filter(weight=3, ofkind__name=‘banana’)), but that uses a sub-select, and there are some databases *cough*MSSQL*cough* that choke on sub-selects.

--
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...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/87375b5f-4951-4f50-9bed-1984645658eb%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Peter of the Norse

Rahm...@Radio1190.org