Django3.2+MySQL5.7下用户积分求和与排名实现（禁用CTE）

模型定义 #

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"

注：同一用户对应多条积分记录

测试数据 #

尝试过的查询代码及问题 #

错误的查询实现 #

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

错误结果 #

<QuerySet [
{'user': 23,   'total': 1771, 'rank': 1}, 
{'user': 1083, 'total': 176,  'rank': 2},
{'user': 771,  'total': 106,  'rank': 8}, <---- 因重复记录导致错误
{'user': 124,  'total': 0,    'rank': 9}
]>

补充信息 #

• 尝试过RANK、DENSE_RANK函数，未得到预期结果
• 原本基于CTE的方案可行，但MySQL5.7不支持CTE，相关代码如下：

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

• 需支持好友圈内排名场景，因此采用count+大于逻辑的排名规则

需求 #

寻求适用于Django3.2 + MySQL5.7的ORM查询方案，正确计算用户总积分并生成排名。

内容的提问来源于stack exchange，提问作者crackaf