Re: Annotate django user queryset with group names

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'))

On Friday, July 22, 2016 at 7:01:08 PM UTC+3, mishbah wrote:

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

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/d07808b2-8cd3-4d02-b07c-ec6545437154%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.