Django Queries Count and update

user_12639110 0
Viewed 109

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'))
count django-queryset group-by django-models django
3 Answers

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]
     )
)
user_67598440

I think you need annotate have a try once.

ASPBookings.objects.annotate(num_athletes=Count('program_type'))
user_146892890

You do not need the field number_asp_sessions. What you are looking for is to access the reverse relationship to ASPBookings from Athlete

you can simply add a method to the model Athlete

class Athlete(models.Model):
    athlete_ref = models.CharField(max_length=10, default=1)
    ...
    ...
    def get_number_of_sessions(self):
        return self.aspbookings_set.all().count()

aspbookings_set refers to the ForeignKey referencing instances of the class ASPBookings viewed from an instance of the class Athlete

user_122821720
Related