Symfony2,Doctrine search and order by distance [英] Symfony2, Doctrine search and order by distance
问题描述
我有一个存储纬度和经度的地点数据库.我想查询数据库以查找特定纬度($latitude
)和经度($longitude
$requestedDistance)内的所有地点>).
I have a database of places which I store the latitude and longitude for. I want to query the database to find all places within a radius ($requestedDistance
) of a specific latitude ($latitude
) and longitude ($longitude
).
以下查询有效并仅返回此半径内的那些地方,但是我如何按距离对它们进行排序,以便最接近的地方在前?过去,我使用原始 SQL 在 SELECT 语句中完成计算并将其设置为距离",然后使用 HAVING distance <$requestedDistance ORDER BY distance
,但是我不确定如何使用 Doctrine Repository 将计算添加到 SELECT 查询中.
The below query works and returns only those places within this radius, however how would I order them by distance so the closest is first? In the past, using raw SQL I have done the calculation within the SELECT statement and set it as 'distance' and then used HAVING distance < $requestedDistance ORDER BY distance
, however I'm not sure how to add the calculation to the SELECT query using Doctrine Repository.
$d = $this->getDoctrine()->getRepository('XXXWebsiteBundle:Locations')->createQueryBuilder('l');
$d
->add('where','l.enabled = 1')
->andWhere('( 3959 * acos( cos( radians('.$latitude.') )
* cos( radians( l.latitude ) )
* cos( radians( l.longitude )
- radians('.$longitude.') )
+ sin( radians('.$latitude.') )
* sin( radians( l.latitude ) ) ) ) < '.$requestedDistance);
$result= $d->getQuery();
更新
感谢@Lazy Ants,我尝试了以下查询:
I've tried the following query thanks to @Lazy Ants:
$d = $this->getDoctrine()->getRepository('XXXWebsiteBundle:Locations')->createQueryBuilder('l');
$d
->select('l')
->addSelect(
'( 3959 * acos(cos(radians(' . $latitude . '))' .
'* cos( radians( l.latitude ) )' .
'* cos( radians( l.longitude )' .
'- radians(' . $longitude . ') )' .
'+ sin( radians(' . $latitude . ') )' .
'* sin( radians( l.latitude ) ) ) ) as distance'
)
->andWhere('l.enabled = :enabled')
->setParameter('enabled', 1)
->andWhere('distance < :distance')
->setParameter('distance', $requestedDistance)
->orderBy('distance', 'ASC');
但是,它返回以下错误:
However, it returns the following error:
`An exception occurred while executing 'SELECT COUNT(*) AS dctrn_count FROM
(SELECT DISTINCT il0 FROM (SELECT l0_.id AS il0, l0_.name AS name2, l0_.address1
AS address14, l0_.address2 AS address25, l0_.postcode AS postcode6, l0_.town AS
town7, l0_.county AS county8, l0_.enabled AS enabled11, l0_.date_created AS
date_created12, l0_.date_modified AS date_modified13, l0_.latitude AS
latitude19, l0_.longitude AS longitude20, 3959 * ACOS(COS(RADIANS(53.51331889999999))
* COS(RADIANS(l0_.latitude)) * COS(RADIANS(l0_.longitude) -
RADIANS(-2.935331099999985)) + SIN(RADIANS(53.51331889999999)) *
SIN(RADIANS(l0_.latitude))) AS sclr21 FROM locations l0_ WHERE l0_.enabled = ?
AND sclr21 < ? ORDER BY sclr21 ASC) dctrn_result) dctrn_table' with params
{"1":1,"2":30}:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sclr21' in 'where clause'`
推荐答案
$d = $this->getDoctrine()->getRepository('XXXWebsiteBundle:Locations')->createQueryBuilder('l');
$d
->select('l')
->addSelect(
'( 3959 * acos(cos(radians(' . $latitude . '))' .
'* cos( radians( l.latitude ) )' .
'* cos( radians( l.longitude )' .
'- radians(' . $longitude . ') )' .
'+ sin( radians(' . $latitude . ') )' .
'* sin( radians( l.latitude ) ) ) ) as distance'
)
->andWhere('l.enabled = :enabled')
->setParameter('enabled', 1)
->having('distance < :distance')
->setParameter('distance', $requestedDistance)
->orderBy('distance', 'ASC');
这篇关于Symfony2,Doctrine search and order by distance的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!