Check if data exists in Django annotate queryset

user_9898642 0
Viewed 515

Environments

  • Django 2.0
  • DRF 3.11
  • mariaDB

I want to output'N' if at least one of the columns is 0, and'Y' if all of them are 1.

Database database example

Model

class UserPoint:
    user = models.ForeignKey('User', others options)
    poinit = models.IntegerField()
    used = models.SmallIntegerField(default=0)
    deleted = models.SmallIntegerField(default=0)

Expected results

  • total_point_used : Sum of points where used is 1
  • used_status : 'N' if any of used has 0 or 'Y' if all values ​​of used are 1
user username total_point_used used_status
1 'AA' 600 'N'
2 'BB' 0 'N'
3 'CC' 500 'Y'

Tried queryset

q = UserPoint.objects.filter(
    deleted=0
).values(

    username=F('user__username')
    .... others values ....

).annotate(
    
    total_point_used=Sum(  # Sum of used point
                Case(
                    When(
                        used=1,
                        then='point',
                    ), output_field=IntegerField(),
                )
            ),

    # I don't know what to do with used_status.
    # tried <- not work
    #used_status=Case(
    #                When(
    #                    used=1,
    #                    then=Value('Y')
    #                ),
    #            default=Value('N'),
    #            output_field=CharField()
    #        )

    # I want something like this.
    used_status=Exists(used=0, then='N', default='Y')
)

How can I get the value I expect in annotate?

django-rest-framework django
1 Answers

Probably you can use Sum with filter:

from django.db.models import Q, Case, When, Sum

# rest of the code
.annotate(
    total_point_used=Sum('point', filter=Q(used=1)),
    used_status=Case(
                    When(
                        used=1,
                        then=Value('Y')
                    ),
                default=Value('N'),
                output_field=CharField()
            )
)
user_26961650
Related