[Fixed]-How can I compare two fields of a model in a query?


from django.db.models import F


  • 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
     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.

Leave a comment