#Count (or sum, or whatever) the number of filled columns from the list. Related to GeneratedField

11 messages · Page 1 of 1 (latest)

heady mist
#

So what I'm trying to achieve is to count of many fields are filled on a model. I thought it might be good use case for GeneratedField, but I fail to put together the query for it.

So let's say I have a model

class ProductTagTranslation(TranslationBase):
    name = models.CharField(max_length=255)
    slug = models.SlugField(max_length=255)

I now want to add field with value 1/2 if there are 2 translated fields but only one of them has been filled. I experimented with queries like
qs = ProductTagTranslation.objects.all().annotate(trans=Count(*[Case(When(~Q(Q(**{f"{fn}__isnull": True}) | Q(**{f"{fn}": ""})), then=Value(1)), default=Value(0)) for fn in fields], output_field=IntegerField(), ) / len(fields)) or
qs = ProductTagTranslation.objects.all().annotate(trans=Sum(*[Case(When(~Q(Q(**{f"{fn}__isnull": True}) | Q(**{f"{fn}": ""})), then=Value(1)), default=Value(0)) for fn in fields], output_field=IntegerField(), ) / len(fields))
where fields = ('name', 'slug') but both of those fail with different errors so I'm thinking perhaps I'm not taking the best approach here. The problem is, that I could also just do Case(...) + Case(...). One per each field, but then how do variable amount of + operations?

heady mist
#

The more I read about this I feel like I can't neither use Count nor Sum for this. And going Case(...) + Case(...) + ... Approach seems the way to go, but how do I create variable amount of sum operations like this?

heady mist
#

I think, the question is if there is a way to do what I want other than hardcoding all the individual cases separately without using list comprehension?

warm crater
#

```py

code

```

for nicer formatting btw

#

your code again I just ran it through ruff for readability

class ProductTagTranslation(TranslationBase):
    name = models.CharField(max_length=255)
    slug = models.SlugField(max_length=255)


fields = ('name', 'slug')


qs = ProductTagTranslation.objects.all().annotate(
    trans=Count(
        *[
            Case(
                When(
                    ~Q(Q(**{f"{fn}__isnull": True}) | Q(**{f"{fn}": ""})), then=Value(1)
                ),
                default=Value(0),
            )
            for fn in fields
        ],
        output_field=IntegerField(),
    )
    / len(fields)
)

qs = ProductTagTranslation.objects.all().annotate(
    trans=Sum(
        *[
            Case(
                When(
                    ~Q(Q(**{f"{fn}__isnull": True}) | Q(**{f"{fn}": ""})), then=Value(1)
                ),
                default=Value(0),
            )
            for fn in fields
        ],
        output_field=IntegerField(),
    )
    / len(fields)
)
heady mist
#

Sweet. IS there an online service for this or you have some savvy commant tool, to do what you just did? I mean I use ruff in the same project but I did never think of using it to format code for discord.

warm crater
#

I'm on VSCode and have autoformatting on save enabled 😬

#

Discord is super picky with formatting anyway, I'd love this to improve at some point

heady mist
#

Same - on pycharm. Hmhh I guess I could have copypasted all this and have it preformatted with pycharm, lol. Dumb blindspot.. anyway. This got me derailed. Do you have suggestions on how to approach/improve my problem?

warm crater
#

Tough. Your query always gives me full integers. Same happens when I move the division into the query expression and cast it to a FloatField, still getting full integers always for some reason.

There's an alternate approach where instead of having many CASEs you have many WHENs inside a single CASE, then you can SUM its results (https://stackoverflow.com/a/50930239). This worked for me (at least hoping I understood your goal correctly):


from core.models import ProductTagTranslation
from django.db.models import Case
from django.db.models import FloatField
from django.db.models import Q
from django.db.models import Sum
from django.db.models import Value
from django.db.models import When

fields = ("name", "slug")
stepsize = 1.0 / len(fields)

whens = []

previous_fields = []
for i, fn in enumerate(fields):
    previous_fields.append(fn)
    num_fields_populated = i + 1

    q = Q()
    for previous_field_name in previous_fields:
        q &= ~Q(Q(**{f"{fn}__isnull": True}) | Q(**{f"{fn}": ""}))

    whens.append(
        When(
            q,
            then=Value(stepsize * num_fields_populated),
        )
    )

q = Sum(
    Case(
        *whens,
        default=Value(0.0),
        output_field=FloatField(),
    )
)
print(q)
qs1 = ProductTagTranslation.objects.all().annotate(trans=q)
print(qs1.query)
print()
for x in qs1:
    print(x.trans)
print()
results = qs1.aggregate(Sum("trans"))
print(results)
heady mist
#

Hmhh. Basically I want the query to count the % of filled field. To know how much of each model is translated.