Annotate django user queryset with group names

[Mishbah Razzaque]

Given that I have a django model that has a ForeignKey that is linked to itself.

    class DjangoModel():

        

        [...]

        successor = models.ForeignKey('self', null=True)

I was able to write a custom django database function like this:

    from django.db.models import BooleanField

    from django.db.models import Func

    class IsNull(Func):

        """

        See docs: https://docs.djangoproject.com/en/1.8/ref/models/database-functions/

        """

        template = '%(expressions)s IS NULL'

        def __init__(self, *args, **kwargs):

            kwargs['output_field'] = BooleanField()

            super(IsNull, self).__init__(*args, **kwargs)

So I can do this:

    queryset = DjangoModel.objects.all()

    queryset = queryset.annotate(**{'is_latest': IsNull('successor')})

and if use `queryset.values()` .. I get

    [{'pk': 1, is_latest': True}, {'pk': 2, 'is_latest': False}, ]

where `is_latest == True` when `successor` field is NULL for an object.

Now I want to do something similar, but have no idea where to start!

The bundled `django.contrib.auth.models.User` has a ManyToMany relations to `django.contrib.auth.models.Group` model

For my project, there are multiple user group types, e.g customer / marketing / finance etc

What I want to do.. is annotate a `User` queryset with `is_FOO` field where `FOO` is a group name. e.g `is_customer` or `is_marketing`

So if I use `.values()` on a queryset, I should get something like this:

    [{'pk': 1, 'is_customer': True, 'is_marketing': False }, {'pk': 1, 'is_customer': True, 'is_marketing': True }]

The group name could be hardcoded, e.g

    queryset.annotate(**{'is_customer': IsGroupMember('customer')}) 

I just need help with the `IsGroupMember` database function!

Is that even possible? Any hints or tips to get me started?

Any help will be genuinely appreciated.  Many Thanks

[Todor Velichkov]

Hello, I don't think sub-classing `Func` would be appropriate here. A much easier way would be to use `RawSQL`

since you want to dynamically check for different group you could write a factory method to build `RawSQLs` for you, something like:

from django.db.models import BooleanField
from django.db.models.expressions import RawSQL

def build_has_group_raw_sql(group_name):
    return RawSQL("""EXISTS(
        SELECT 1 FROM `auth_group`
        WHERE `auth_group`.`name` = %s
            AND `auth_group`.`id` IN (
                SELECT `auth_user_groups`.`group_id`
                FROM `auth_user_groups`
                WHERE `auth_user_groups`.`user_id` = `auth_user`.`id`
            )
    )""", (group_name,), output_field=BooleanField())

Now you can use this function as follows:

User.objects.all().annotate(is_customer=build_has_group_raw_sql('customer'))

[mishbah]

You are awesome! Thank you!

[mishbah]

While I was waiting for suggestions, I did some more research learnt about django Conditional Expressions.

Docs: https://docs.djangoproject.com/en/1.9/ref/models/conditional-expressions/

    from django.contrib.auth.models import User

    from django.db.models import BooleanField

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

    query = When(groups__name__in=[CUSTOMER_GROUP_NAME, ], then=Value(1))

    qs2 = User.objects.annotate(

        is_customer=Case(query, default=Value(0), output_field=BooleanField()))

Posting it here.. someone else may find it helpful. 

[Todor Velichkov]

This is actually a pretty good solution and I totally forgot about it!

It will help people for sure!