CODEWITHSUNDEEP
pythonsqldjango
Ryan Saxe
Ryan Saxe

Reputation: 17829

Django filtering in exclusively

The following is Django for filtering if the query is in a list:

options = [1,2,3]
result = Example.objects.filter('something__property__in'=options)

so here something is a ForeignKey relation (with multiple relations) to Example

I want result to be all the Examples that have a something of 1 AND 2 AND 3. The example code above will be 1 OR 2 OR 3. This would be exclusive!

examples

the following would not be in result:

examples = Example.objects.all()
things = examples[0].something.all()
for thing in things: print thing.property
#1
#2
#4
#7

the following would be in result:

examples = Example.objects.all()
things = examples[0].something.all()
for thing in things: print thing.property
#1
#2
#8
#9
#3

the reason being the second example has everything that is in options where the first example has 1 and 2, but not 3!

Is there a simple way to do this in Django?

the only thing that I can think to do is just filter with the example I gave above, get all properties in a list. And compare the list with options in the following python function:

def exclusive_in(list1,list2):
    count = 0
    for i in list1:
        if i in list2:
            count += 1
    if count == len(list2):
        return True
    else:
        return False

I feel like Django could do this in its queries to the database, which would be much more efficient. Any ideas?

note:

this must work for an arbitrary number of items in options, it could be any number greater than 2, even 10 or 100 (although 100 is not likely, it is still possible)

also note that options will be populated by strings

Upvotes: 4

Views: 385

Answers (1)

alko
alko

Reputation: 48297

The only way I am aware of is to pass in query some custom condition with extra method.

Django won't allow you to pass this condition with tables param, so it has to be in where. See following example with django.contrib.auth models (Admin group in the example has all the permissions):

>>> from django.contrib.auth import models
>>> permissions_id = [1,2]
>>> where = """(select count(1)
...               from auth_group_permissions gp
...              where gp.permission_id in ({})
...                and gp.group_id = auth_group.id) = {}"""    
>>> where = where.format(','.join(map(str, permissions_id)), 
...                      str(len(permissions_id )))
>>> models.Group.objects.extra(where=[where]).all()
[<Group: Admin>]
>>> print models.Group.objects.extra(where=[where]).all().query
SELECT `auth_group`.`id`, `auth_group`.`name` 
  FROM `auth_group` WHERE (select count(1)
                      from auth_group_permissions gp
                     where gp.permission_id in (1,2)
                       and gp.group_id = auth_group.id) = 2

Upvotes: 3

Related Questions