0

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.

sgt_pepper85
  • 421
  • 4
  • 14

3 Answers3

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.

Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
  • Thanks Willem, could you please just explain where I should enter this block of code? – sgt_pepper85 Dec 07 '20 at 17:17
  • @sgt_pepper85: in the view where you want to list the maximum weights per execise. – Willem Van Onsem Dec 07 '20 at 17:18
  • Yeah, I'm receiving a FieldError `Cannot resolve keyword 'weight' into field` – sgt_pepper85 Dec 07 '20 at 17:21
  • @sgt_pepper85: sorry made a typo: it is `'workout__weight'` – Willem Van Onsem Dec 07 '20 at 17:23
  • Morning Willem, sorry but I am still struggling with this. I set your query to a context variable of `context['m_weight'] = ` and this returns one instance of each exercise the user has created that also contains a workout, I assume it is the instance with the highest weight value, but doesn't return the values for their workouts. If I attach it to the extra attribute with {{m_weight.max_weight}} it returns nothing. – sgt_pepper85 Dec 08 '20 at 05:31
  • 1
    @sgt_pepper85: `m_weight` is not a *single* `Exercise` these are *all* the `Exercise`s the user has done. You thus *iterate* over the exercises, and print the `max_weight`, so `{% for exc in m_weight %}{{ exc }}: {{ exc.max_weight }}{% endfor %}`. – Willem Van Onsem Dec 08 '20 at 08:03
  • Ahh yes of course. Ok I have it now. Brilliant! Thank you very much – sgt_pepper85 Dec 08 '20 at 08:25
0

Given a user you need to annotate each excersise with max weight.

from django.db.models import Max

user.workout_set.values('exercise').annotate(max_weight=Max('weight'))

this should work

Adithya
  • 1,688
  • 1
  • 10
  • 18
  • Hi thanks so much for responding. So this query goes in my Workout model? Should it be part of a method or as-is? And how will I then access this data in my template? – sgt_pepper85 Dec 07 '20 at 15:11
  • This will return a queryset with each result containing `exercise` and `max_weight` values. I'm not very familiar with using templates, but i think you can iterate over this queryset in the template with `{% for i in some_list %}` syntax. – Adithya Dec 07 '20 at 15:18
  • Sorry I am still confused about where to put this line of code. – sgt_pepper85 Dec 07 '20 at 15:50
0

Instead of using aggregation, an easy way of doing it is ordering and then using distinct on exercise:

Workout.objects.filter(user=user)\
    .order_by('exercise', '-weight')\
    .distinct('exercise')

This will give you a list of all workouts with the biggest weight for each exercise.

Martí
  • 2,651
  • 1
  • 4
  • 11
  • Hi thanks Marti. I tried adding your code to my `get_context_data` method in my view but my template returns `TemplateSyntaxError Could not parse the remainder: '-weight' from 'max-weight'` – sgt_pepper85 Dec 07 '20 at 15:57
  • It's a `TemplateSyntaxError` so it's very likely that it is a bug in your template (specially if you changed it). Also, notice that this expression returns a queryset of workouts (the ones with the biggest weight); so your `max-weight` probably should just be `weight`. – Martí Dec 07 '20 at 16:34
  • Sorry about that, I had chosen a bad variable name. However I now receive `NotSupportedError DISTINCT ON fields is not supported by this database backend` – sgt_pepper85 Dec 08 '20 at 05:34
  • That error is quite self explanatory: the database backend that you are using does not support the `.distinct('exercise')` part. – Martí Dec 08 '20 at 20:13