django orm multiple points ranking

Question

I've a model name Points which store the user points on the base of it's actions.

class Points(CreateUpdateModelMixin):
    class Action(models.TextChoices):
        BASE = 'BASE', _('Base')
        REVIEW = 'REVIEW', _('Review')
        FOLLOW = 'FOLLOW', _('Follow')
        VERIFIED_REVIEW = 'VERIFIED_REVIEW', _('Verified Review')
        REFERRAL = 'REFERRAL', _('Referral')        
        ADD = 'ADD', _('Add')
        SUBTRACT = 'SUBTRACT', _('Subtract')

    user = models.ForeignKey(User, on_delete=models.CASCADE)
    points = models.IntegerField()
    action = models.CharField(max_length=64, choices=Action.choices, default=Action.BASE)

    class Meta:
        db_table = "diner_points"

Please note that there are multiple rows for the same user. For the past few days I'm trying to write a query to get the total_points of the use and also the rank of that user.

Using: Django 3.2 MySQL 5.7

I want to know input of you guys. Thanks.

I wrote this query and many other like it. But none of them give the results I want. Let's suppose the data is something like this.

user	points
771	221
1083	160
1083	12
1083	10
771	-15
1083	4
1083	-10
124	0
23	1771

The current query I have written is this...

innerquery = (
        DinerPoint.objects
        .values("user")
        .annotate(total=Sum("points"))
        .distinct()
)
query = (
    DinerPoint.objects
    .annotate(
        total = Subquery(
            innerquery.filter(user=OuterRef("user")).values("total")
        ),
        rank = Subquery(
            DinerPoint.objects
            .annotate(
                total = Subquery(
                    innerquery.filter(user=OuterRef("user")).values("total")
                ),
                rank=Func(F("user"), function="Count")
            )
            .filter(
                Q(total__gt=OuterRef("total")) |
                Q(total=OuterRef("total"), user__lt=OuterRef("user"))
            )
            .values("rank")[:1]
        )
    )
)
query.values('user', 'total', 'rank').distinct().order_by('rank')

But this give the results like this

<QuerySet [
{'user': 23,   'total': 1771, 'rank': 1}, 
{'user': 1083, 'total': 176,  'rank': 2},
{'user': 771,  'total': 106,  'rank': 8}, <---- Issue beacuse of dups entries
{'user': 124,  'total': 0,    'rank': 9}
]>

I've tried RANK, DENSE RANK and didn't got the results I wanted.

The only way I got the results I wanted I throught the Common Table Expression(CTE). But unfortunately I can't use that because of mysql version 5.7 in produciton.

P.S I'm using the count and greater than beacause of my use case. I have a use case where we have to get rank in the user friends.

The working code using CTE by django_cte (You can ignore this beacuse of mysql 5.7 ;) )

def get_queryset(user=None, following=False):
    if not user:
        user = User.objects.get(username="king")

    innerquery = (
        DinerPoint.objects
        .values("user", "user__username", "user__first_name", "user__last_name", "user__profile_fixed_url",
                "user__is_influencer", "user__is_verified", "user__instagram_handle")
        .annotate(total=Sum("points"))
        .distinct()
    )

    if following:
        innerquery = innerquery.filter(Q(user__in=Subquery(user.friends.values('id'))) |
                                        Q(user = user))

    basequery = With(innerquery)

    subquery = (
        basequery.queryset()
        .filter(Q(total__gt=OuterRef("total")) |
                Q(total=OuterRef("total"), user__lt=OuterRef("user")))
        .annotate(rank=Func(F("user"), function="Count"))
        .values("rank")
        .with_cte(basequery)
    )

    query = (
        basequery.queryset()
        .annotate(rank=Subquery(subquery) + 1)
        .select_related("user")
        .with_cte(basequery)
    )

    return query

Answer 1

I have done this using the Func expression field. The final query which works for me is attached below in case you are looking for an answer.

rank=Func(
    F("user"),
    function="Count",
    template="%(function)s(DISTINCT %(expressions)s)",
),

Final query

def get_queryset(self):
    following = self.request.query_params.get("following", False)

    innerquery = (
        DinerPoint.objects.values("user").annotate(total=Sum("points")).distinct()
    )

    basequery = DinerPoint.objects

    if following:
        innerquery = innerquery.filter(
            Q(user__in=Subquery(self.request.user.friends.values("id")))
            | Q(user=self.request.user)
        )
        basequery = basequery.filter(
            Q(user__in=Subquery(self.request.user.friends.values("id")))
            | Q(user=self.request.user)
        )

    query = (
        basequery.annotate(
            total=Subquery(
                innerquery.filter(user=OuterRef("user")).values("total")
            ),
            rank=Subquery(
                DinerPoint.objects.annotate(
                    total=Subquery(
                        innerquery.filter(user=OuterRef("user")).values("total")
                    ),
                    rank=Func(
                        F("user"),
                        function="Count",
                        template="%(function)s(DISTINCT %(expressions)s)",
                    ),
                )
                .filter(
                    Q(total__gt=OuterRef("total"))
                    | Q(total=OuterRef("total"), user__lt=OuterRef("user"))
                )
                .values("rank")
            )
            + 1,
        )
        .values(
            "user",
            "user__username",
            "user__first_name",
            "user__last_name",
            "user__profile_fixed_url",
            "user__is_influencer",
            "user__is_verified",
            "user__instagram_handle",
            "total",
            "rank",
        )
        .distinct()
    )

    return query