I got stuck in a query in django. I have two models,
Class Course(models.Model):
provider = ForeignKey(Provider)
rating = FloatField()
Class Provider(models.Model):
...
I want to query a list of courses, but no two courses can be provided by the same provider. that is, only the most relevant course from each provider is selected. I did this,
Course.objects.filter(xxxx).distinct('provider')
This works OK, but if I want to add order_by to this query, like
Course.objects.filter(xxxx).distinct('provider').order_by('rating')
an error occurs. In django documentation, the fields in order_by() must start with the fields in distinct(), in the same order.
yet, I still cannot find any other workarounds to do the same thing.
any ideas? Thank you so much.
You need to look at this problem from the 'reverse' perspective and use "annotage". Here is how you'll get your desired output in a view:
#views.py
from xxx.models import Course, Provider
from django.db.models import Min
from django.shortcuts import render
def home(request):
t = Provider.objects.annotate(best_rating = Min('course__rating')).filter('xxx').order_by('best_rating')
return render(request, 'home.html', {
't':t,
})
So what's happening here:
First of all "annotate" is Django's way to do what is generally known as "group by" statements. Unfortunately they delivery different results depending on what object they are called upon. So in our case we have to look at "the best course of each provider" instead of "top rated courses and their providers".
In our .annotate(best_rating = Min('course__rating')) statement, the inner clause returns the best course (assuming 1.0 Rating is the best, use Max() if it is the opposite way around). The return value is assigned to a virtual model attribute called best_rating, which you can call in a template like any other model member. It just won't be stored in the database.
The String 'course__rating' is a special string. It tells Django ORM what field we want to find the minimum of. This string has the format "<MODEL>__<FIELD>". Although a provider is not aware of his relationship to a course, Django will do this reverse lookup for us. So this string basically says: "give me all rating fields of courses that have a relationship with providers"
annotate returns a QuerySet like many other querySet Methods, hence you can filter and order the new set as you please.
Of course you can filter before the annotation as well, but be aware this might affect your final result when you do aggregations like finding a min() or max() value.
Basic template example:
#home.html
{% for e in t %}
{{ e.name }} {{ e.best_rating }}<br>
{% endfor %}
Have a look at the Django Documentation for further information about annotations and reverse lookups.
Update 01.04.2014: If you want to keep your Query driven by a list of Courses, you can use the following statement using F() Statements
from xxx.models import Course, Provider
from django.db.models import Min, F
from django.shortcuts import render
def home(request):
t = Course.objects.annotate(best_rating = Min('provider__course__rating')).filter(rating = F('best_rating'))
return render(request, 'home.html', {
't':t,
})
This Query will annotate by providers and course ratings. But this alone would return all providers with an additional column with the lowest rating of a course. So we need to filter each row on this new value. To do this, we need a F() Expression, that matches a value for each row.
I got stuck in a query in django. I have two models,
Class Course(models.Model):
provider = ForeignKey(Provider)
rating = FloatField()
Class Provider(models.Model):
...
I want to query a list of courses, but no two courses can be provided by the same provider. that is, only the most relevant course from each provider is selected. I did this,
Course.objects.filter(xxxx).distinct('provider')
This works OK, but if I want to add order_by to this query, like
Course.objects.filter(xxxx).distinct('provider').order_by('rating')
an error occurs. In django documentation, the fields in order_by() must start with the fields in distinct(), in the same order.
yet, I still cannot find any other workarounds to do the same thing.
any ideas? Thank you so much.
You need to look at this problem from the 'reverse' perspective and use "annotage". Here is how you'll get your desired output in a view:
#views.py
from xxx.models import Course, Provider
from django.db.models import Min
from django.shortcuts import render
def home(request):
t = Provider.objects.annotate(best_rating = Min('course__rating')).filter('xxx').order_by('best_rating')
return render(request, 'home.html', {
't':t,
})
So what's happening here:
First of all "annotate" is Django's way to do what is generally known as "group by" statements. Unfortunately they delivery different results depending on what object they are called upon. So in our case we have to look at "the best course of each provider" instead of "top rated courses and their providers".
In our .annotate(best_rating = Min('course__rating')) statement, the inner clause returns the best course (assuming 1.0 Rating is the best, use Max() if it is the opposite way around). The return value is assigned to a virtual model attribute called best_rating, which you can call in a template like any other model member. It just won't be stored in the database.
The String 'course__rating' is a special string. It tells Django ORM what field we want to find the minimum of. This string has the format "<MODEL>__<FIELD>". Although a provider is not aware of his relationship to a course, Django will do this reverse lookup for us. So this string basically says: "give me all rating fields of courses that have a relationship with providers"
annotate returns a QuerySet like many other querySet Methods, hence you can filter and order the new set as you please.
Of course you can filter before the annotation as well, but be aware this might affect your final result when you do aggregations like finding a min() or max() value.
Basic template example:
#home.html
{% for e in t %}
{{ e.name }} {{ e.best_rating }}<br>
{% endfor %}
Have a look at the Django Documentation for further information about annotations and reverse lookups.
Update 01.04.2014: If you want to keep your Query driven by a list of Courses, you can use the following statement using F() Statements
from xxx.models import Course, Provider
from django.db.models import Min, F
from django.shortcuts import render
def home(request):
t = Course.objects.annotate(best_rating = Min('provider__course__rating')).filter(rating = F('best_rating'))
return render(request, 'home.html', {
't':t,
})
This Query will annotate by providers and course ratings. But this alone would return all providers with an additional column with the lowest rating of a course. So we need to filter each row on this new value. To do this, we need a F() Expression, that matches a value for each row.
0 commentaires:
Enregistrer un commentaire