Django annotate queryset by predicate and count results-CodePudding

I have two models:

class Game(models.Model):
    id = models.AutoField(primary_key=True)


class Score(models.Model):
    id = models.AutoField(primary_key=True)
    game = models.ForeignKey(Game, related_name="score", on_delete=models.CASCADE)
    first_score = models.IntegerField(blank=True)
    second_score = models.IntegerField(blank=True)
    is_rusk = models.BooleanField(blank=True)

And I got a queryset of Game objects:

[
    {
        "id": 314317035,
        "score": [
            {
                "first_score": 5,
                "second_score": 1,
                "is_rusk": false
            }
        ]
    },
    {
        "id": 311298177,
        "score": [
            {
                "first_score": 5,
                "second_score": 2,
                "is_rusk": false
            }
        ]
    },
    {
        "id": 310278749,
        "score": [
            {
                "first_score": 5,
                "second_score": 2,
                "is_rusk": false
            }
        ]
    },
    {
        "id": 309866238,
        "score": [
            {
                "first_score": 5,
                "second_score": 0,
                "is_rusk": true
            }
        ]
    },
    {
        "id": 307926664,
        "score": [
            {
                "first_score": 5,
                "second_score": 0,
                "is_rusk": true
            }
        ]
    },
    {
        "id": 306047964,
        "score": [
            {
                "first_score": 4,
                "second_score": 5,
                "is_rusk": false
            }
        ]
    },
    {
        "id": 304881611,
        "score": [
            {
                "first_score": 5,
                "second_score": 3,
                "is_rusk": false
            }
        ]
    },
    {
        "id": 304468136,
        "score": [
            {
                "first_score": 5,
                "second_score": 2,
                "is_rusk": false
            }
        ]
    },
]

I want to annotate this queryset with rusks_cnt, it will be count of objects with is_rusk=True, If there is a way to not add this to every object, just as one field, that would be good too.

I think easiest way to do it like this:

cnt = queryset.filter(score__is_rusk=True).count()

But when I'm trying to annotate like this:

cnt = queryset.filter(score__is_rusk=True).count()
queryset = queryset.annotate(cnt=cnt)

It says:

QuerySet.annotate() received non-expression(s): 2.

I've also tried:

queryset = queryset.annotate(
         rusk_cnt=Sum(
                Case(When(score__is_rusk=True, then=1)), output_field=IntegerField()
            )
        )

But results are:

[
    {
        "id": 279658929,
        "rusk_cnt": 1
    },
    {
        "id": 279796553,
        "rusk_cnt": null
    },
    ...
]

Also I wondering is just using .count() will lead to bad performance?

CodePudding user response：

You can annotate with a Value:

from django.db.models import Value

cnt = queryset.filter(score__is_rusk=True).count()
queryset = queryset.annotate(cnt=Value(cnt))

But this will add the same value: the number of Score objects for the Games in the queryset to all Game objects, which does not make much sense.

If you want to annotate the Game objects with True the number of objects where Score with is_rusk=True, you can work with:

from django.db.models import Q, Sum

queryset.annotate(
    rusk_cnt=Sum('score', filter=Q(score__is_rusk=True))
)

CodePudding user response：

Annotate is for calculating on every entry. If you want to calculate for the entire queryset, use Aggregate.

Difference between Django's annotate and aggregate methods?