CODEWITHSUNDEEP
pythondjango
tread
tread

Reputation: 11098

Get a list of users along with there group membership?

I want to get a queryset along with their group membership to avoid expensive additional database hits and to make managing stuff in the template much easier.

Initially I tried to prefetch the groups a user belongs to:

def get_queryset(self):
    User.objects.filter(is_active=True).prefetch_related('groups')

But then it makes it difficult to show and hide things in the template based on the groups:

{{ user.groups.all }}

gives

<QuerySet [<Group: Managers>]>

Then I though perhaps an annotation may be better with a is_manager and is_head eg:

from django.db.models import BooleanField, Case, Value, When

def get_queryset(self):
    '''Return both active and inactive users for managers
    Annotate with is_manager = True/False
    '''
    query = When(groups__name__in=[MANAGER_GROUP_NAME, ], then=Value(1))
    return self.model.objects.all().annotate(
            is_manager=Case(
                query,
                default=Value(0),
                output_field=BooleanField()
            )
        )

Which worked okay but gave me an error: 

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'my_site.auth_group.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

But would be easy to use in the template with:

{% if user.is_manager %} Manager {% else %} Normal user {% endif %}

Is there an easier way to do it?

Edit:

The final query:

self.model.objects.prefetch_related('groups').all().annotate(
            is_manager=Case(
                When(
                    groups__id__exact=1, then=True
                ), default=False, output_field=BooleanField()
            )
        ).values("pk", "id", "email", "is_manager")

Upvotes: 2

Views: 289

Answers (1)

priyankvex
priyankvex

Reputation: 6040

Nice attempt with your approach.

Firstly, if possible consider using group ids instead of group names to identify managers. Will dramatically improve query performance and spelling related bugs.

Following is a quick snippet that I tried and is working for me. (on postgres).

User.objects.prefetch_related("groups").annotate(
    is_super=Case(
        When(
            groups__id__in=[1, 2,3 ], then=1
        ), default=0, output_field=IntegerField()
    )
).order_by("id").values_list("id", "username", "is_super")

Upvotes: 1

Related Questions