How can I compare two fields of a model in a query?

Question

I'm writing a management command which will filter a product's original price with suggested prices.

I have a product model which looks like :

class Suggestion(models.Model):
    ....
    price = models.IntegerField()

class Product(models.Model):
    price = models.IntegerField()
    suggestions = models.ManyToManyField(Suggestion)

I want to filter all products whose price is equal to minumum suggestion. Something should like :

Product.objects.filter(price = minumum(suggestions))

AND

I want to filter products where the suggestions contains the Product's original price. Something should like :

Product.objects.filter(price__in = self.suggestions)

The problem is I can't use a for-loop to look each Product's minumum suggestion and as you guess I can't use object's self either, so how can I compare two fields of a model in a query ?

score 30 · Accepted Answer · edited Nov 12 '15 at 19:24

30

from django.db.models import F
Product.objects.filter(price__in=F('suggestions'))

edited Nov 12 '15 at 19:24

Stefan Magnuson

870
1
10
10

answered Aug 14 '11 at 00:59

mossplix

3,783
2
26
31

1

Thanks! That works nicely. Yet, there's a small error: suggestions must be in quotation marks inside F('suggestions'). – Simon Steinberger Oct 04 '13 at 22:05
1

how about two F? for example two annotated fields? – Hojat Modaresi May 05 '20 at 09:40

score 2 · Answer 2 · answered Jun 14 '12 at 21:30

Product.objects.filter(price = minumum(suggestions))

suggestions is not a field on Product (and the columns passed to F should have quotes, like F('suggestions') not F(suggestions)). So that's no good.

The raw SQL for this is something like this, which joins onto a subquery that gets the minimum price for every product, then filters the list down to those products whose price == the min price.

SELECT * FROM product
  LEFT JOIN (
     SELECT _products_suggestions.product_id, MIN(price) as min_price
     FROM suggestion
     RIGHT JOIN _products_suggestions
     GROUP BY _products_suggestions.product_id
     ) AS min_suggestions ON min_suggestions.product_id = product.id
  WHERE product.price = min_suggestions.price

You cannot perform (as of 1.4) a custom join in this way using the django ORM. You will need to use a raw SQL query.

Product.objects.filter(price__in = self.suggestions)

self.suggestions, assuming we are in a Product instance method, is not a list, so far it's a RelatedSetManager. I doubt, though, that you want to get all the products that have one of the suggested prices of the current (aka self) product. That seems odd.

What it sounds like you want is a list of products that have a suggestion that matches one of the suggested prices.

You'll need raw SQL again. :-/

SELECT * FROM product
  LEFT JOIN _products_suggestions ON _products_suggestions.product_id = product.id
  LEFT JOIN suggestion ON _products_suggestions.suggestion_id = suggestion.id
  WHERE suggestion.price = product.price

That would do it, I think. RIGHT JOIN might be faster there, I'm not sure, but anyway you'd end up with a list of products and suggestions that have the same price.

How can I compare two fields of a model in a query?

2 Answers2

Linked