Create a query on the division of two fields (that span a FK relationship)

Question

I have an object:

class Image(models.Model):
    width = models.IntegerField(...)
    height = models.IntegerField(...)

class Foo(models.Model):
    title = ...
    image = models.ForeignKey(Image ...)

and I want to try and retrieve all Foo objects that have a related image with a similar aspect ratio. In a perfect world, something like:

ratio = width/height
min = ratio - 0.25
max = ratio + 0.25

similar = ImageMeta.objects \
              .annotate(ratio=Div('image__width', 'image__height')\
              .filter(ratio__gte=min).filter(ratio__lte=max)

but there is no such divison aggregation function. This answer suggests using extra, i.e.

.extra({ 'select' : 'width/height' )

but because the width and height are in a different table, I'm not sure how to span the relationship (i.e. write the SQL) to do this. Could anyone offer some help?

score 1 · Accepted Answer · answered Jan 13 '12 at 02:53

Consider using F() objects instead of aggregation.

This query multiplies by the ratio to do straight comparisons on a single field:

Foo.objects.filter(image__width__gte=F('image__height') * min_ratio, 
                   image__width__lte=F('image__height') * max_ratio)

Create a query on the division of two fields (that span a FK relationship)

1 Answers1