mysql - Django raw() 查询，WHERE 子句中的计算字段

Question

我想知道在使用计算字段时 raw() 方法的语法是否有任何限制。这是一个简单的例子:

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

上面的代码按预期工作(结果按计算字段“dist”排序)，但是当我添加 WHERE 子句时，例如:

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

我得到(1054，“‘where 子句’中的未知列‘dist’”)

到目前为止，我似乎无法在 WHERE 子句中使用计算字段，但我可以在 ORDER BY 语句中使用它。请分享您的经验。谢谢。

Answer 1

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

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

您可以:

• 重复从句:

  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''')
  

• 做子选择:

  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''')