How to compare two model fields in a query?

Question

I am writing a management team that will filter the starting price of a product with the offered prices.

I have a product model that looks like this:

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 the minimum offer. Something should like:

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

and

I want to filter products in which offers contain the original price of the product. Something should like:

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

The problem is that I cannot use the for-loop to view each sentence of the minumum product, and, as you believe, I cannot use the self object, since I can compare two model fields in the query?

Answer 1

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

Answer 2

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

suggestions not a field in Product (and columns passed to F must have quotation marks, for example F('suggestions') not F(suggestions) ). So nothing good.

The raw SQL for this is something like that that joins a subquery that gets the lowest price for each product, and then filters the list down to those products whose price is the lowest 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 do (since 1.4) a user connection this way using django ORM. You will need to use a raw SQL query .

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

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

It looks like you want it to be a list of products that have an offer matching one of the offered prices.

You will need raw SQL .: - /

 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 may be faster, I'm not sure, but in any case you will have a list of products and offers that have the same price.