Django Queries Count and update

Issue

I have the following two models ASPBoookings and Athlete. The Athlete model is linked to the ASPBookings model by the foreign key named athlete.

I was recently shown queries/subqueries with Views and I have been trying to use them (still learning) to provide a count of all bookings assigned to each athlete within the ASPBookings table. Once I then have the information I need the "number_of_asp_sessions" within the Athlete model to be automatically updated with each of the athletes bookings count.

e.g. Athlete ID 1, may have two bookings assigned. Athlete ID 2, may have one booking assigned. The number_of_asp_sessions should then show these numbers for each of the athletes.

Hope this makes sense and thanks for advance for any help. Appreciate it.

Below is the code:

ASPBookings Model

class ASPBookings(models.Model):
    asp_booking_ref = models.CharField(max_length=10, default=1)
    program_type = models.CharField(max_length=120, default='asp')
    booking_date = models.DateField()
    booking_time = models.CharField(max_length=10, choices=booking_times)
    duration = models.CharField(max_length=10, choices=durations, default='0.5')
    street = models.CharField(max_length=120)
    suburb = models.CharField(max_length=120)
    region = models.CharField(max_length=120, choices=regions, default='Metro')
    post_code = models.CharField(max_length=40)
    organisation_type = models.CharField(max_length=120,choices=organisation_types, default='Government School')
    audience_number = models.CharField(max_length=10)
    presentation_form = models.CharField(max_length=120, choices=presentation_form_options, default='Face to Face')
    contact_name = models.CharField(max_length=80)
    email = models.EmailField()
    phone_number = models.CharField(max_length=120)
    comments = models.TextField()
    status = models.CharField(max_length=80, choices=statuses, default='TBC')
    email_sent = models.BooleanField(default=False)
    athlete = models.ForeignKey(Athlete, default= '1', on_delete=models.CASCADE)

    def __str__(self):
        return self.contact_name
        
    # return URL after the POST has been submitted.
    def get_absolute_url(self):
        return reverse('vistours:success')

Athlete Model.

class Athlete(models.Model):
    athlete_ref = models.CharField(max_length=10, default=1)
    athlete_name = models.CharField(max_length=80)
    email = models.EmailField()
    phone_number = models.CharField(max_length=120)
    home = models.CharField(max_length=120)
    education = models.CharField(max_length=120)
    sport = models.CharField(max_length=120, choices=sports, default='1500m Runner')
    notes = models.TextField(default='None')
    gender = models.CharField(max_length=120, choices=genders, default='Not Specified')
    para_athlete = models.BooleanField(blank=True)
    working_with_children = models.BooleanField(blank=True)
    expiry_date = models.DateField(blank=True, null=True)
    available = models.BooleanField(blank=True)
    available_from = models.DateField(blank=True, null=True)
    bfbw = models.BooleanField(blank=True)
    latest_bfbw_session = models.DateField(blank=True, null=True)
    number_bfbw_sessions = models.CharField(blank=True, null=True, max_length=10)
    asp = models.BooleanField(blank=True)
    latest_asp_session = models.DateField(blank=True, null=True)
    number_asp_sessions = models.CharField(blank=True, null=True, max_length=10)
    tours = models.BooleanField(blank=True)
    latest_tours_session = models.DateField(blank=True, null=True)
    number_tours_sessions = models.CharField(blank=True, null=True, max_length=10)

    def __str__(self):
        return self.athlete_name

    # return URL after the POST has been submitted.
    def get_absolute_url(self):
        return reverse('home')

VIEW

def count_bookings(request):

    Athlete.objects.annotate(number_of_asp_sessions=Count('aspbookings')).update(number_asp_sessions=F('number_of_asp_sessions'))
    return HttpResponseRedirect(request.META.get('HTTP_REFERER'))

Solution

Have a try at below:

Athlete.objects.annotate(number_of_asp_sessions=Count('aspbookings')).update(number_asp_sessions=F('number_of_asp_sessions')

This will count all asp bookings per athlete, then update the number_asp_sessions field based on the annotated field for all the athletes in one go.

EDIT:

I tried it and somehow I’m getting:

    FieldError: Aggregate functions are not allowed in this query

Seems I cannot use the annotated field for updating. For now this should work:

objs = []
for a in Athlete.objects.annotate(number_of_asp_sessions=Count('aspbookings')):
    a.number_asp_sessions = a.number_of_asp_sessions
    objs.append(a)
Athlete.objects.bulk_update(objs, ['number_asp_sessions'])

EDIT:

Ended up using subqueries 😉 This should also work:

Athlete.objects.update(
     number_asp_sessions=Subquery(
          Athlete.objects.annotate(number_of_asp_sessions=Count('aspbookings')).filter(pk=OuterRef('pk')).values('number_of_asp_sessions')[:1]
     )
)

Answered By – BrianD

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0