php - DQL 关联和 lat lon 距离

Question

实体A有一个字段name

实体B有一个字段city

实体C有字段lat和lon

实体A与实体有一对多关联 B命名bees

实体B与实体有多对一关联 C命名cees

现在我想选择全部 A距离 Y 点 X 公里以内的实体。

X = 20

Y = 纬度:52.25，经度:4.53

DQL: SELECT a, ( 6371 * acos( cos( radians(52.25) ) * cos( radians( c.lat ) ) * cos( radians( c.lon ) - radians(4.53) ) + sin( radians(52.25) ) * sin( radians( c.lat ) ) ) ) AS distance FROM A a LEFT JOIN a.bees b LEFT JOIN b.cees c WHERE distance < 20

当我执行这个 DQL 时，我得到:

An exception occurred while executing 'SELECT m0_.name AS name0, m0_.id AS id11, (6371 * ACOS(COS(RADIANS(52.25)) * COS(RADIANS(p1_.lat)) * COS(RADIANS(p1_.lon) - RADIANS(4.53)) + SIN(RADIANS(52.25)) * SIN(RADIANS(p1_.lat)))) AS sclr12, FROM table_a m0_ LEFT JOIN table_b m2_ ON m0_.id = m2_.a_id LEFT JOIN table_c p1_ ON m2_.b_id = p1_.id WHERE sclr12 < 20':

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sclr12' in 'where clause'`

我在这里做错了什么？非常感谢任何帮助!

约拉姆

Answer 1

您不能在 where 子句中引用别名。

您可能会在这里找到解决方案:SQL Use alias in Where statement