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
– AKS, Commented Jun 7, 2016 at 4:04
Yes but it does not matter as it's progress is not recorded after intensity_level=7 — Thinker
– Thinker, Commented Jun 8, 2016 at 18:24

e4c5 · Accepted Answer · 2016-06-09 15:11:42Z

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 9, 2016 at 15:11

answered Jun 6, 2016 at 10:39

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Daniel Roseman Over a year ago

Even simpler with range: exercise_id__range=(1, 7).

Thinker Over a year ago

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'

e4c5 Over a year ago

huh no. My raw sql query is the exact equivelent of your raw sql query. all those extra ANDs are redunant

Thinker Over a year ago

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.

e4c5 Over a year ago

that's a more complex query. Please update your question with this infor and don't forget to post your django model.

|

Collectives™ on Stack Overflow

Mysql query in django view

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related