如何比较查询中模型的两个字段？

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 ?

问题是我不能使用for循环查看每个产品的最小建议，因为你猜我也不能使用对象的自我，那么如何在查询中比较模型的两个字段？

2 个解决方案

#1

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

#2

Product.objects.filter(price = minumum(suggestions))
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.

建议不是产品上的字段（传递给F的列应该有引号，如F（'建议'）而不是F（建议））。所以这不好。

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.

原始SQL就是这样的，它连接到一个子查询，该子查询获得每个产品的最低价格，然后将列表过滤到价格=最小价格的那些产品。

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.

您无法使用django ORM以这种方式执行（自1.4开始）自定义连接。您将需要使用原始SQL查询。

Product.objects.filter(price__in = self.suggestions)
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.

self.suggestions，假设我们在Product实例方法中，不是列表，到目前为止它是一个RelatedSetManager。不过，我怀疑你想获得所有具有当前（又称自我）产品价格之一的产品。这看起来很奇怪。

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. :-/

你需要再次使用原始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 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.

我想，那会做到的。 RIGHT JOIN可能会更快，我不确定，但无论如何，你最终会得到一个价格相同的产品和建议列表。

#1

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

#2

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