Django ORM: All users which have at least one group

Question

I want to find all users which are at least in one group.

I found a solution

from django import setup

setup()

from django.contrib.auth.models import User
user_without_group = User.objects.update_or_create(username='user-without-group')[0]
user_without_group.groups.clear()
query = User.objects.filter(groups__isnull=False).values_list('username', flat=True)
print (query.query)
print list(query)
assert user_without_group.username not in query

... but the solution is not nice. Is there a more obvious solution than groups__isnull=False?

update

Why I think this solution is not nice: I think this is not obvious. It is not easy to read and understand. If you show this to someone who knows Python, but has never used the django ORM, I don't think he will immediately understand what this means.

User.objects.filter(groups__isnull=False)

@ivissani I updated the question and explained why I think this is not nice. — guettli, Jan 28 '19 at 09:24
If it's a matter of readability I think you have two options, the first one is to explain your query using comments (which, by the way, I find to be a good practice as understanding the intent of queries is not always very easy). The second one would be to write a custom Lookup with a more meaningful name, let's say 'exists' so your query becomes `User.objects.filter(groups__exists=False)`. This lookup should do the same as te `IsNull` lookup. — ivissani, Jan 28 '19 at 12:38
@ivissani I read the books "clean code" and "clean coder" and since then I avoid comments. Comments lie. Just five minutes ago I came across an old comments which was just plain wrong. The code changed, the variable and method names were easy to understand. The comment was dated. I removed it, since it was wrong and not needed (in this case). — guettli, Jan 28 '19 at 13:04
Another practice that I find useful in Django is to override the queryset for your model to add common filters as methods. So you can add a method to your `User` queryset called `with_groups()` or whatever you find appropriate, that, internally, performs the filtering. Then you achieve two things 1) you modularize your code allowing for future changes to be easily implemented and 2) you can be more descriptive of what you are trying to do — ivissani, Jan 28 '19 at 13:44

score 5 · Answer 1 · answered Jan 25 '19 at 15:29

5

I'm not sure what you have against groups__isnull=False; it's absolutely fine. Note though that it's exactly equivalent to groups=None, which you might find a bit nicer.

answered Jan 25 '19 at 15:29

Daniel Roseman

588,541
66
880
895

1

I tested it. I guess you mean `groups=None` is the opposite of `groups__isnull=False`. – guettli Jan 28 '19 at 09:25
`User.objects.exclude(groups=None)` is how it would be used to get what you're after – Fush Jan 31 '19 at 01:11
1

@guettli I would use this and put a comment like: `#Be sure users have at least one group`, comments are python code too, and you can/should use them to add clarity to your code. – Raydel Miranda Feb 01 '19 at 17:58

score 2 · Answer 2 · answered Jan 25 '19 at 14:35

2

You can also do:

Group.objects.values_list('user').distinct('user')

And you will get unique users only with 1 and more Group.

answered Jan 25 '19 at 14:35

Sergey Pugach

5,561
1
16
31

score 1 · Answer 3 · answered Jan 25 '19 at 18:46

User.objects.filter(groups__isnull=False).values_list('username') produces simple SQL:

SELECT "users_user"."username" FROM "users_user" 
INNER JOIN "users_user_groups" ON ("users_user"."id" = "users_user_groups"."user_id") 
WHERE "users_user_groups"."group_id" IS NOT NULL

Filtering null on a fk shown exactly the same in docs.

score 1 · Answer 4 · answered Jan 30 '19 at 19:07

A less SQL implementation specific solution would be to use either an Exists subquery or a Count

User.objects.annotate(
    has_groups=Exists(
        User.groups.through.objects.filter(
            user=OuterRef('pk'),
        )
    )
).filter(has_groups=True)

User.objects.annotate(
    groups_count=Count('groups'),
).filter(groups__gte=1)

I doubt these solution would perform any faster than groups__isnull=True's inner join though. It'd be great if m2m fields exposed an __exists lookup so you could do filter(groups__exists=True) and avoid most of this boilerplate.

Yes, `filter(groups__exists=True)` would be very nice. – guettli Jan 30 '19 at 19:27 — guettli, Jan 30 '19 at 19:27

score 1 · Accepted Answer · answered Feb 01 '19 at 18:19

1

You can consider adding a readable custom lookup field like group__at_least_one=True by Custom Lookups

answered Feb 01 '19 at 18:19

Yugandhar Chaudhari

3,831
3
24
40

Django ORM: All users which have at least one group

5 Answers5