Mysql query in django view

Question

I do not have much knowledge of mysql. My django model is as follows:

 class Exercise_state(models.Model):
     exercise = models.ForeignKey(Exercise, blank=True, null=True)
     user = models.ForeignKey(User, blank=True, null=True) 
     intensity_level = models.IntegerField(default='1')
     progress = models.IntegerField(default='0')
     current_date = models.DateField(auto_now=True)
     user_rating = models.IntegerField(default='0')

I want to retrieve count of user_id's which satisfy exercise_id=1 corresponding intensity_level=7 and so on for all exercise_id's till 7. In short, users who have reached intensity_level=7 in all the exercises.

I have written one query which is as follows:

select count(user_id) from demo_exercise_state where 
exercise_id=1 and intensity_level=7 and
exercise_id=2 and intensity_level=7 and
exercise_id=3 and intensity_level=7 and 
exercise_id=4 and intensity_level=7 and
exercise_id=5 and intensity_level=7 and
exercise_id=6 and intensity_level=7 and
exercise_id=7 and intensity_level=7;

As far as I can cross check results from my database directly no user has yet completed his training (i.e. who has reached intensity_level=7 in all exercise types), so it returns the count as '0'.

I am sure this can query be optimized as there is quite much repeatation, but I am not sure how.

Also I want to execute the same (i.e. get the same result) in my django view. There I have something like

all_exercise_finish_count = Exercise_state.objects.filter(exercise_id=1, intensity_level=7).count()

How can I reflect the same in this django view?

Can a user do an exercise with `intensity_level=7` more than one time? — AKS, Jun 07 '16 at 04:04
Yes but it does not matter as it's progress is not recorded after intensity_level=7 — Thinker, Jun 08 '16 at 18:24

e4c5 · Answer 1 · 2016-06-09T15:11:42.900

1

Update Completely changing my answer because the subsequent discussion in the comment made it clear that the OPs intentions were quite different from what it first appeared to be.

You can find the users who have completed all the 7 excercises at intensity 7 in the following way

from django.db.models.import Count

Exercise_state.objects.annotate(
    c = Count('exercise').filter(c=7, exercise_id= 7)

First form of the answer is below:

The original raw query can be greatly simplified with between

expr BETWEEN min AND max

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 13.2, “Type Conversion in Expression Evaluation”, but applied to all the three arguments.

select count(user_id) from demo_exercise_state where 
exercise_id BETWEEN 1 and 7 AND intensity_level=7

And the django query can simply be

all_exercise_finish_count = Exercise_state.objects.filter(exercise_id__gte=1, intensity_level=7, excercise_id__lte=7).count()

edited Jun 09 '16 at 15:11

answered Jun 06 '16 at 10:39

e4c5

52,766
11
101
134

1

Even simpler with [`range`](https://docs.djangoproject.com/en/1.9/ref/models/querysets/#range): `exercise_id__range=(1, 7)`. – Daniel Roseman Jun 06 '16 at 12:11
Unfortunately, the originally query and the query in answer give me different results. My originally query gives me '0' whereas your query gave me '1'! I think it considered any exercise_id between 1 and 7 with intensity_level 7 and not all exercise_id from 1 to 7 with intensity_level '7' – Thinker Jun 06 '16 at 13:14
huh no. My raw sql query is the exact equivelent of your raw sql query. all those extra ANDs are redunant – e4c5 Jun 06 '16 at 13:41
I think I should re-frame my end-requirement: I want to retrieve count of user_id's which satisfy exercise_id=1 corresponding intensity_level=7 and so on for all exercise_id's till 7. In short, users who have reached intensity_level=7 in all the exercises. – Thinker Jun 06 '16 at 14:02
that's a more complex query. Please update your question with this infor and don't forget to post your django model. – e4c5 Jun 06 '16 at 14:07
Updated the question – Thinker Jun 06 '16 at 14:18
Do people need to do excercise 1 before they can do 2 and so on? or can anyone pick any excercise at random? – e4c5 Jun 06 '16 at 14:24
No there is no such constraint. They can do exercises in any order, and finish exercises independently – Thinker Jun 06 '16 at 14:26

Mysql query in django view

1 Answers1