OK, I am at my wits end on this query. I would like to build a query that returns the top performance in each event (separated by men and women). Here is my model structure:
class Athlete(models.Model):
Graduation = models.IntegerField()
Athlete = models.CharField(max_length=200, null=True)
Male = models.BooleanField()
Active = models.BooleanField()
First = models.CharField(max_length=200, blank=True)
Last = models.CharField(max_length=200, blank=True)
updated = models.DateTimeField(auto_now=True)
created = models.DateTimeField(auto_now_add=True)
def __str__(self):
return self.Athlete
class Meet(models.Model):
MeetName = models.TextField()
State = models.BooleanField()
Indoor = models.BooleanField()
Location = models.CharField(max_length=200, null=True)
updated = models.DateTimeField(auto_now=True)
created = models.DateTimeField(auto_now_add=True)
def __str__(self):
return self.MeetName
class Event(models.Model):
EventName = models.CharField(max_length=200, null=True)
FieldEvent = models.BooleanField()
Current = models.BooleanField()
Relay = models.BooleanField()
Order = models.IntegerField()
MeasurementSystem = models.CharField(max_length=20)
updated = models.DateTimeField(auto_now=True)
created = models.DateTimeField(auto_now_add=True)
def __str__(self):
return self.EventName
class Performance(models.Model):
EventID = models.ForeignKey(Event, on_delete=models.SET_NULL,null=True)
Mark = models.CharField(max_length=200, null=True)
MarkRawLarge = models.IntegerField()
MarkRawSmall = models.DecimalField(decimal_places=3,max_digits=20)
MeetID = models.ForeignKey(Meet, on_delete=models.SET_NULL,null=True)
PerformanceNote = models.CharField(max_length=10,null=True,blank=True)
CY = models.IntegerField(null=True)
EventDate = models.DateField(null=True)
Notes = models.TextField(null = True,blank=True)
Archive = models.BooleanField()
StateChamp = models.BooleanField(null=True)
AthleteID = models.ManyToManyField(Athlete)
Confirmed = models.BooleanField(null=False,default=False)
Locked = models.BooleanField(null=False,default=False)
updated = models.DateTimeField(auto_now=True)
created = models.DateTimeField(auto_now_add=True)
I have 4 queries that successfully produce the top ten athletes in a particular category as shown here (code excerpt from views.py) Note: I use annotate for "AthleteID" since some events have multiple athletes (e.g. relays), so this prevents a relay from showing up multiple times in the query expression. (I used sum, but count would have worked also I think)
views.py
if FieldEvent == 1:
performancesmen = Performance.objects.filter(EventID=pk,AthleteID__Male=True,Archive=0,Confirmed=1).\
order_by('-MarkRawLarge','-MarkRawSmall').annotate(total_athletes=Sum('AthleteID'))[0:10]
performanceswomen = Performance.objects.filter(EventID=pk,AthleteID__Male=False,Archive=0,Confirmed=1).\
order_by('-MarkRawLarge','-MarkRawSmall').annotate(total_athletes=Sum('AthleteID'))[0:10]
else:
performancesmen = Performance.objects.filter(EventID=pk,AthleteID__Male=True,Archive=0,Confirmed=1).\
order_by('MarkRawLarge','MarkRawSmall').annotate(total_athletes=Sum('AthleteID'))[0:10]
performanceswomen = Performance.objects.filter(EventID=pk,AthleteID__Male=False,Archive=0,Confirmed=1).\
order_by('MarkRawLarge','MarkRawSmall').annotate(total_athletes=Sum('AthleteID'))[0:10]
Note: Field Events are largest first and running events are smallest first. Also, the "RawMarkLarge/RawMarkSmall" are used to hold values depending on if a run or distance. The "Large" holds either minutes or feet (always integer) and the small holds the seconds/inches (sometimes a fraction so has to be a float).
How can I write the Django Query expression to shows the top athlete in each event (for all events)? It would be ok to have 4 queries (Men Field, Men Run, Women Field,Women Run)
Thanks.