How to parse the result of applicant__count in Model.objects.values("applicant", 'counter').annotate(Count("applicant")) to counter field?

I have a model with these fields although there are other fields but this is my MRE:

class Application(models.Model):     
    applicant = models.ForeignKey(User, on_delete=models.CASCADE,    to_field='email')     
    company = models.CharField(max_length=100)     
    counter = models.PositiveIntegerField(editable=False, default=0)

I want to find the number of applications in the table for each applicant and parse the value automatically to the counter field. In my views.py, I have been able to use:

model = Application.objects.values('applicant','counter').annotate(Count("applicant"))

which returns correct values:

{'applicant': 'test@users.com', 'counter': 1, 'applicant__count': 2} {'applicant': 'second@user.org', 'counter': 1, 'applicant__count': 4}

But I am unable to extract the value of `applicant__count` and parse it directly to the counter field in models.py.

I tried using the update, update_or_create method but I'm not able to update the model. I also tried django signals pre_save and post_save but they keep incrementing every value. For example, one applicant can have many job applications but instead of returning the total number of job applications for an applicant, django signals increments all the applications in the table.

Is there any way to automatically save the result of `applicant__count` to my counter field? I would really appreciate any help.

I would advise not to work with a counter field. Storing aggregates is often not a good idea: you can determine these when necessary.

I also tried django signals pre_save and post_save but they keep incrementing every value.

Exactly, that is the main reason not to store aggregates. It requires updating in all sorts of scenarios: if the target model is removed, updated, created, etc. If the queries are done in bulk, signals don't even run, making it often close to impossible to keep the counter in perfect sync.

You can remove the counter field:

from django.conf import settings


class Application(models.Model):
    applicant = models.ForeignKey(
        settings.AUTH_USER_MODEL, on_delete=models.CASCADE, to_field='email'
    )
    company = models.CharField(max_length=100)
    # counter = models.PositiveIntegerField(editable=False, default=0)

and calculate it when necessary with .annotate(…) [Django-doc]: User.objects.annotate(counter=Count('application')).

Where here annotate the User object, annotating the application object means we lose the User object, and thus only have an email address that we need to link back to.

Storing the aggregate on the Application model also does not make much sense either: it would mean that the application is storing how many times the applicant of the Application has applied?

Note: It is normally better to make use of the settings.AUTH_USER_MODEL [Django-doc] to refer to the user model, than to use the User model [Django-doc] directly. For more information you can see the referencing the User model section of the documentation.

Вернуться на верх