Django raw（）查询，WHERE子句中的计算字段 [英] Django raw() query, calculated field in WHERE clause

问题描述

我想知道在使用计算字段时，raw（）方法的语法是否有限制。
这是一个简单的例子：

I'm wondering if there are any limitations on syntax of raw() method when using calculated fields. Here is a quick example:

Company.objects.raw('''SELECT *,core_location.a + core_location.b as dist
FROM core_location,core_company  
ORDER BY dist''')

上面的代码按预期工作（结果按计算字段'dist'排序），但是当我添加WHERE子句时，例如：

The above code works as expected (the results are sorted by calculated field 'dist'), but when I add WHERE clause, for example:

Company.objects.raw('''SELECT *,core_location.a + core_location.b as dist
FROM core_location,core_company
WHERE dist<10  
ORDER BY dist''')

我得到
（1054，Unknown column'dist'在'where子句'）

i'm getting (1054, "Unknown column 'dist' in 'where clause'")

到目前为止，看起来我不能在WHERE子句中使用计算字段，但是我可以在ORDER BY语句中使用它。请分享你的经验。谢谢。

So far it looks like I cannot use calculated field in WHERE clause, but I can use it in ORDER BY statement. Please share your experience. Thank you.

推荐答案

它实际上与Django本身无关，但与MySQL的工作方式无关。

It actually has nothing to do with Django itself, but with the way MySQL works.

您不能在WHERE条件中使用别名，因为WHERE子句评估先于别名评估。

You can't use aliases in WHERE conditions, because WHERE clause evaluation precedes the aliases evaluation.

您可以：

• 重复这个子句： p>
  
  

• Repeat the clause:

Company.objects.raw('''SELECT *,core_location.a + core_location.b as dist
FROM core_location,core_company
WHERE (core_location.a + core_location.b)<10    
ORDER BY dist''')

执行子选择：

Do a subselect:

Company.objects.raw('''SELECT * FROM (
    SELECT *,core_location.a + core_location.b as dist
    FROM core_location,core_company            
) as subselect
WHERE dist<10  
ORDER BY dist''')

这篇关于Django raw（）查询，WHERE子句中的计算字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！