CODEWITHSUNDEEP
djangodjango-queryset
Andrew Cross
Andrew Cross

Reputation: 1911

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

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]

Upvotes: 1

Views: 2490

Answers (1)

patrickn
patrickn

Reputation: 2561

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.

Upvotes: 3

Related Questions