Friday, July 22, 2016

Re: Annotate django user queryset with group names

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

It will help people for sure!

On Saturday, July 23, 2016 at 1:29:42 AM UTC+3, mishbah wrote:
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. 

On Friday, 22 July 2016 22:02:09 UTC+1, mishbah wrote:
You are awesome! Thank you!

On Friday, 22 July 2016 21:41:58 UTC+1, Todor Velichkov wrote:
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):
        """
        """
        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/7c05e64d-6d00-4bdc-8d8e-c9dbf340e38c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

No comments:

Post a Comment