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!
This question is related to
django
django-models
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()
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
Source: Stackoverflow.com