Django ManyToMany filter()

Question

I have a model:

class Zone(models.Model):
    name = models.CharField(max_length=128)
    users = models.ManyToManyField(User, related_name='zones', null=True, blank=True)

And I need to contruct a filter along the lines of:

u = User.objects.filter(...zones contains a particular zone...)

It has to be a filter on User and it has to be a single filter parameter. The reason for this is that I am constructing a URL querystring to filter the admin user changelist: http://myserver/admin/auth/user/?zones=3

It seems like it should be simple but my brain isn't cooperating!

I'm not sure if I get you right - isn't `User.objects.filter(zones__id=)` or `User.objects.filter(zones__in=)` good for this? — Tomasz Zieliński, Feb 07 '10 at 21:16
That's ok :) BTW `User.objects.filter(zones__in=)` should probably be `User.objects.filter(zones__id__in=)` — Tomasz Zieliński, Feb 08 '10 at 00:08
Just wanted to point out for anyone Googling this, that it only works if related_name is set. zone_set wouldn't work, for example. Wasted a good half-hour on that :-) — , Dec 06 '12 at 13:27
@user391538 That can't be true though. If you do not set related_name, related_query_name is the name of the model. You can read [django docs on related_query_name](https://docs.djangoproject.com/en/4.1/ref/models/fields/#django.db.models.ForeignKey.related_query_name) — FAYEMI BOLUWATIFE, Oct 19 '22 at 19:19

istruble · Accepted Answer · 2013-06-10T15:58:03.520

240

Just restating what Tomasz said.

There are many examples of FOO__in=... style filters in the many-to-many and many-to-one tests. Here is syntax for your specific problem:

users_in_1zone = User.objects.filter(zones__id=<id1>)
# same thing but using in
users_in_1zone = User.objects.filter(zones__in=[<id1>])

# filtering on a few zones, by id
users_in_zones = User.objects.filter(zones__in=[<id1>, <id2>, <id3>])
# and by zone object (object gets converted to pk under the covers)
users_in_zones = User.objects.filter(zones__in=[zone1, zone2, zone3])

The double underscore (__) syntax is used all over the place when working with querysets.

edited Jun 10 '13 at 15:58

answered Feb 08 '10 at 03:34

istruble

13,363
2
47
52

19

double underscore (argh. 3 hours lost to that one) – reabow Jan 14 '15 at 09:37
Can you please say, what to do if I want the users who are in a set of zones not just any one of them? Lets say find user who are in zone1, zone3, .. and zone 10 – FRR Oct 03 '18 at 05:26
Look at the `...__in` examples after `# filtering on a few zones, by id`. Those show filtering for multiple ids/objects ( in this case). Just pass in the zone1, zone3, and zone10 ids/objects you care about. Or add a 4th if needed. – istruble Nov 05 '18 at 17:00
Thx. I was only filtering against a single value, instead of an array containing the single value. – zypro Oct 04 '19 at 06:29
2

What if I want to filter by exact list of zones. like `exact_zones = User.objects.filter(zones=[1,2])` I got error says it should be ID not a list. But I need these exact zones. – Ali Husham Aug 20 '22 at 11:55
See `...__in` examples. In your case `.filter(zones__in=[1, 2])`. – istruble Aug 22 '22 at 17:25

score 54 · Answer 2 · edited Aug 08 '20 at 20:00

54

Note that if the user may be in multiple zones used in the query, you may probably want to add .distinct(). Otherwise you get one user multiple times:

users_in_zones = User.objects.filter(zones__in=[zone1, zone2, zone3]).distinct()

edited Aug 08 '20 at 20:00

Amin Mir

640
8
15

answered Oct 06 '14 at 14:09

QB.

541
4
2

Sam Mason · Answer 3 · 2020-07-03T13:33:58.003

11

another way to do this is by going through the intermediate table. I'd express this within the Django ORM like this:

UserZone = User.zones.through

# for a single zone
users_in_zone = User.objects.filter(
  id__in=UserZone.objects.filter(zone=zone1).values('user'))

# for multiple zones
users_in_zones = User.objects.filter(
  id__in=UserZone.objects.filter(zone__in=[zone1, zone2, zone3]).values('user'))

it would be nice if it didn't need the .values('user') specified, but Django (version 3.0.7) seems to need it.

the above code will end up generating SQL that looks something like:

SELECT * FROM users WHERE id IN (SELECT user_id FROM userzones WHERE zone_id IN (1,2,3))

which is nice because it doesn't have any intermediate joins that could cause duplicate users to be returned

edited Jul 03 '20 at 13:33

answered Jul 01 '20 at 16:15

Sam Mason

15,216
1
41
60

1

Hiya. This isn't an answer in itself. You should add a comment or edit QB's answer rather than add an additional partial answer. – Andy Baker Jul 01 '20 at 20:07
Yeah - if you want to edit your answer so it's complete in it's own right (unless you've got enough karma to edit QB's answer?) then that would be the best bet. Ideally on StackOverflow there's "one correct answer". It doesn't usually work out quite that neatly but it's worth aiming for. – Andy Baker Jul 02 '20 at 14:19
@AndyBaker agreed! in retrospect QB's answer should probably be a comment on istruble's answer, while I think mine is distinct enough to warrant separate answer, but ah well – Sam Mason Jul 03 '20 at 13:53

score 2 · Answer 4 · answered Dec 23 '22 at 03:20

You can also lookup simply by name, without __in:

class Publication(models.Model):
    ...

class Article(models.Model):
    publications = models.ManyToManyField(Publication)

Article.objects.filter(publications=1)
# or
Article.objects.filter(publications=p1)

Works with related_name as well from my tests.

per Django docs

score 1 · Answer 5 · answered Mar 10 '23 at 17:01

I had a similar problem where some users ended with a free role and also a paid role, so I needed to select users that have two specific roles on the many to many field.

I did something like this, works like a charm

roles = [premium_role, default_role]

DiscordUser.objects.filter(guild=guild)
    .annotate(freemium=Count('roles', filter=Q(roles__in=roles)))
    .filter(freemium=2)
    .values('id')

If you want this to be more dynamic, you can change this line with

.filter(freemium=len(roles))

score 0 · Answer 6 · answered Sep 18 '22 at 12:50

You could use Q object if you have more complex query in the scenario like this question.

based on this question suppose you want to filter Users with specific id(e.g. 10) and filter Zones with the name that starts with 'europe'

query would be like this:

filtered_zone = Q(zones__in=Zone.objects.filter(name__startswith='europe')
result = User.objects.filter(Q(id=10) & Q(filtered_zone))

Django ManyToMany filter()

6 Answers6

Linked

Related