2👍
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.