How do I use Django's "extra" method to filter by a calculated property?

Question

I've got a search function in my app that receives "cities" and "duration" inputs (both lists) and returns the top 30 matching "package" results sorted by package "rating".

It would be easy to implement if all the parameters were columns, but "duration" and "rating" are calculated properties. This means that I can't use a standard Django query to filter the packages. It seems that Django's "extra" method is what I need to use here, but my SQL isn't great and this seems like a pretty complex query.

Is the extra method what I should be using here? If so, what would that statement look like?

Applicable code copied below.

#models.py
class City(models.Model):
    ...
    city = models.CharField(max_length = 100)

class Package(models.Model):
    ....
    city = models.ManyToManyField(City, through = 'PackageCity')

    @property
    def duration(self):
        duration = len(Itinerary.objects.filter(package = self))
        return duration

    @property
    def rating(self):
        #do something to get the rating
        return unicode(rating)


class PackageCity(models.Model):
    package = models.ForeignKey(Package)
    city = models.ForeignKey(City)


class Itinerary(models.Model):
    # An Itinerary object is a day in a package, so len(Itinerary) works for the duration
    ...
    package = models.ForeignKey(Package)




#functions.py
def get_packages(city, duration):
    cities = City.objects.filter(city = city) # works fine
    duration_list = range(int(duration_array[0], 10), int(duration_array[1], 10) + 1) # works fine

    #What I want to do, but can't because duration & rating are calculated properties
    packages = Package.objects.filter(city__in = cities, duration__in = duration_array).order_by('rating')[:30]

You should save rating and duration somewhere using Signals, this will do it easier — sacabuche, Jan 21 '13 at 16:47
@sacabuche what do signals have to do with filtering a queryset? — patrickn, Jan 21 '13 at 17:32
@patrickn nothing at all but it will be better to save this data instead of calculate every time IMHO — sacabuche, Jan 21 '13 at 17:36

score 3 · Accepted Answer · answered Jan 21 '13 at 17:31

First off, don't use len() on Querysets, use count(). https://docs.djangoproject.com/en/dev/ref/models/querysets/#when-querysets-are-evaluated

Second, assuming you're doing something like calculating an average rating with your rating property you could use annotate: https://docs.djangoproject.com/en/dev/ref/models/querysets/#annotate

Then you can do something like the following:

queryset = Package.objects.annotate({'duration': Count('related-name-for-itinerary', distinct=True), 'rating': Avg('packagereview__rating')})

Where "PackageReview" is a fake model I just made that has a ForeignKey to Package, and has a "rating" field.

Then you can filter the annotated queryset as described here: https://docs.djangoproject.com/en/dev/topics/db/aggregation/#filtering-on-annotations (Take note of the clause order differences between annotate -> filter, and filter -> annotate.

Properties are calculated at run time, so you really can't use them for filtering or anything like that.

Ahhh, this looks promising, thanks. I'll play around with this and try to get it working. — Andrew Cross, Jan 21 '13 at 18:09
Got it working. Thanks a ton, this is a lot cleaner than I thought it was going to be. The query I ended up using was: packages = Package.objects.filter(city__in = cities).annotate(avg_rating = Avg('rating__rating'), total_duration = Count('itinerary', distinct = True)).filter(total_duration__in = duration_array).order_by('avg_rating')[:30] — Andrew Cross, Jan 21 '13 at 18:34

How do I use Django's "extra" method to filter by a calculated property?

1 Answers1