Django ORM多点排名

huangapple go评论56阅读模式
英文:

django orm multiple points ranking

问题

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 user 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 others 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 Django ORM多点排名 )

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
英文:

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 = &#39;BASE&#39;, _(&#39;Base&#39;)
        REVIEW = &#39;REVIEW&#39;, _(&#39;Review&#39;)
        FOLLOW = &#39;FOLLOW&#39;, _(&#39;Follow&#39;)
        VERIFIED_REVIEW = &#39;VERIFIED_REVIEW&#39;, _(&#39;Verified Review&#39;)
        REFERRAL = &#39;REFERRAL&#39;, _(&#39;Referral&#39;)        
        ADD = &#39;ADD&#39;, _(&#39;Add&#39;)
        SUBTRACT = &#39;SUBTRACT&#39;, _(&#39;Subtract&#39;)

    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 = &quot;diner_points&quot;

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(&quot;user&quot;)
        .annotate(total=Sum(&quot;points&quot;))
        .distinct()
)
query = (
    DinerPoint.objects
    .annotate(
        total = Subquery(
            innerquery.filter(user=OuterRef(&quot;user&quot;)).values(&quot;total&quot;)
        ),
        rank = Subquery(
            DinerPoint.objects
            .annotate(
                total = Subquery(
                    innerquery.filter(user=OuterRef(&quot;user&quot;)).values(&quot;total&quot;)
                ),
                rank=Func(F(&quot;user&quot;), function=&quot;Count&quot;)
            )
            .filter(
                Q(total__gt=OuterRef(&quot;total&quot;)) |
                Q(total=OuterRef(&quot;total&quot;), user__lt=OuterRef(&quot;user&quot;))
            )
            .values(&quot;rank&quot;)[:1]
        )
    )
)
query.values(&#39;user&#39;, &#39;total&#39;, &#39;rank&#39;).distinct().order_by(&#39;rank&#39;)

But this give the results like this

&lt;QuerySet [
{&#39;user&#39;: 23,   &#39;total&#39;: 1771, &#39;rank&#39;: 1}, 
{&#39;user&#39;: 1083, &#39;total&#39;: 176,  &#39;rank&#39;: 2},
{&#39;user&#39;: 771,  &#39;total&#39;: 106,  &#39;rank&#39;: 8}, &lt;---- Issue beacuse of dups entries
{&#39;user&#39;: 124,  &#39;total&#39;: 0,    &#39;rank&#39;: 9}
]&gt;

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 Django ORM多点排名 )

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

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

    if following:
        innerquery = innerquery.filter(Q(user__in=Subquery(user.friends.values(&#39;id&#39;))) |
                                        Q(user = user))

    basequery = With(innerquery)

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

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

    return query

答案1

得分: 0

以下是翻译好的部分:

我已经使用 `Func` `expression` 字段完成了这个对我有效的最终查询如下以防您正在寻找答案

Final query
``` py
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

<details>
<summary>英文:</summary>
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.
``` py
rank=Func(
F(&quot;user&quot;),
function=&quot;Count&quot;,
template=&quot;%(function)s(DISTINCT %(expressions)s)&quot;,
),

Final query

def get_queryset(self):
    following = self.request.query_params.get(&quot;following&quot;, False)

    innerquery = (
        DinerPoint.objects.values(&quot;user&quot;).annotate(total=Sum(&quot;points&quot;)).distinct()
    )

    basequery = DinerPoint.objects

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

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

    return query

huangapple
  • 本文由 发表于 2023年2月14日 19:54:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/75447469.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定