How to find the highest value of a ForeignKey field for a specific user in Django

Question

I am building an app that allows users to record their workouts. First they will create an exercise, (e.g Bench Press), and then they will complete a form to show how much weight they were able to lift for that specific exercise. Their results will display below the form. There will be many workout forms, relating to many different workouts. The workouts and exercises will also be specific to each user.

Here is my models.py:

from django.contrib.auth.models import User

class Exercise(models.Model):
    name = models.CharField(max_length=100)

class Workout(models.Model):
    user = models.ForeignKey(Profile, on_delete=models.CASCADE)
    weight = models.DecimalField(default=0.0, max_digits=5, decimal_places=1)
    exercise = models.ForeignKey(Exercise, on_delete=models.CASCADE, default=None)

class Profile(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)

What I now want to do is be able to show the user what their max lift was for each different workout, but can't figure out how to retrieve this information. I have searched for the answer online and it seems that using aggregate or annotate might be the way to go, but I have tried a bunch of different queries and can't get it to show what I need. Hope somebody can help.

Answer 1

You can annotate your Exercises with the maximum weight for the Workouts of a given user with:

from django.db.models import Max

Exercise.objects.filter(
    workout__user=someprofile
).annotate(
    max_weight=Max('workout__weight')
)

The Exercise objects that arise from this queryset will have an extra attribute .max_weight that contains the maximum weight for that exercise for someprofile.