selecting an item whose relations are all in some desired set

[David Xiao]

Django is missing the "not in" operator and I have a situation where the workarounds (using ~Q or using exclude) don't seem to give a satisfactory solution.

Suppose I have two models:

from django.db.models import Model, ForeignKey

class Bundle(Model):

  pass

class Item(Model):

  bundle = ForeignKey(Bundle, related_name="items")

Suppose special_items is a list of Items and I want to select a bundle whose items are all in special_items, and I would like to use only 1 DB query.  The most natural way to do this would be Bundle.objects.filter(items__not_in=special_items), except that Django doesn't have a not_in lookup.

The following doesn't work:

Bundle.objects.filter(items__in=special_items)

because it will select Bundles where there exists a related item in special_items, but it will also select bundles that have items not in special_items.

I couldn't find a good way to use ~Q() and exclude to achieve this.  The problem is that a query on the related field is a "there exists" statement, and what I need is a "there exists a related item that is not in special_items", whereas the only ways I could come up with using ~Q() and exclude will end up giving statements of the form "all related items are not in special_items".

Am I missing an easy solution for this?  (Again only interested in solutions using a single query.)

[Vitor Freitas]

Hi David,

Perhaps you can try using exclude instead of filter:

https://docs.djangoproject.com/en/1.9/topics/db/queries/#retrieving-specific-objects-with-filters

But wasn't 100% clear for me what you are trying to achieve, so I don't know if the following query will be give you the desired result:

Bundle.objects.exclude(items__in=special_items)

Anyway, this is how we perform a not_in operation in Django

--
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/35f1e364-28ea-4d26-bca5-6a265758430c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Vitor Freitas

vitorfs.com

[David Xiao]

Hi Vitor,

Sorry I realized that my example should have used a ManyToManyField instead of a 1-to-many.  Let me try again:

class Bundle(Model)

  items = ManyToManyField("Item")

class Item(Model)

  pass

(So one item can belong many Bundles and one Bundle can have many Items.)

Suppose I've created items item1, item2, item3 and I have special_items = [item1, item2].  Suppose also the following Bundles exist in the database:

1. Bundle containing item1

2. Bundle containing item2

3. Bundle containing item1, item2

4. Bundle containing item2, item3

5. Bundle containing item1, item2, item3

I want to select all bundles where *every item in the bundle* is in special_items.  This means selecting bundles 1, 2, 3 but not bundles 4, 5 (because item3 is not in special_items).

The query you gave would select no bundles, and the query Bundle.objects.filter(items__in=[special_items]) would select all the bundles.

Dave

[florian]

an exclude on all items that are not in special_items?

exclude(items__in=[not_special_items])

Florian

[David Xiao]

Hi Florian,

That's technically correct, but the universe of all possible items might be very large or even infinite so it's not really practical to do it that way.

Dave

--
You received this message because you are subscribed to a topic in the Google Groups "Django users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-users/tv49fShowIQ/unsubscribe.
To unsubscribe from this group and all its topics, 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/5ecb6508-5a62-4cb3-a5bc-204ec0162483%40googlegroups.com.

[Florian Iragne]

Le 17/05/2016 16:18, David Xiao a écrit :
> Hi Florian,
> That's technically correct, but the universe of all possible items
> might be very large or even infinite so it's not really practical to
> do it that way.
>

I understand, but i don't now your case precisely. In my case, i do this
for a few thousands of "items" and it works like i want. However, it's
clearly not optimal.

You can use raw sql if you want/can

Florian