Django Filtering of Data to get Top Athlete for Each Event

Viewed 17

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.

0 Answers
Related