Django查询计数和更新

Question

我有以下两种型号ASPBoookings和Athlete。运动员模型由外国关键指定运动员链接到ASPBookings模型。

我最近看到了带有视图的查询/子查询，我一直在尝试使用它们(仍在学习(来提供ASPBookings表中分配给每位运动员的所有预订的计数。一旦我得到了我需要的信息；asp_sessions的数目"；在运动员模型中，将随着每个运动员的预订计数而自动更新。

运动员ID 1(可以分配两个预订。运动员ID 2，可能分配了一个预订。然后，number_of_asp_sessions应该显示每个运动员的这些数字。

希望这是有意义的，并感谢advance的任何帮助。非常感谢。

以下是代码：

ASP预订模型

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')

运动员模型

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')

查看

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'))

Answer 1

试试下面的：

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

这将计算每个运动员的所有asp预订，然后根据所有运动员的注释字段一次性更新number_asp_sessions字段。

编辑：

我试过了，不知怎么的，我得到了：

FieldError: Aggregate functions are not allowed in this query

似乎我无法使用带注释的字段进行更新。目前，这应该有效：

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

编辑：

最终使用了子查询；(这也应该起作用：

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

Answer 2

我想你需要注释一下。

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

Answer 3

您不需要字段number_asp_sessions。您想要的是从Athlete访问与ASPBookings的反向关系

您可以简单地在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是指从类Athlete的实例查看的引用类ASPBookings的实例的ForeignKey