dsjklb0205
2014-10-23 12:01
浏览 83
已采纳

Laravel 4:如何将这个复杂的原始地理SQL查询转换为使用Eloquent?

So I'm trying to find the closest points to another particular point within a certain distance, and I'm using the haversine formula for this. The raw query itself is:

SELECT id, 
( 3959 * acos( cos( radians(input_lat) ) * cos( radians( map_loc_lat ) ) * cos( radians( map_loc_long ) - radians(input_long) ) + sin( radians(input_lat) ) * sin( radians( map_loc_lat ) ) ) ) 
AS distance FROM posts HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

Where input_lat and input_long are my predefined coordinates that I want my center searching point to begin from, and map_loc_lat and map_loc_long are the posts' points.

I'm trying to use Eloquent's selectRaw() method to do a portion of the heavy lifting, like so:

$query->selectRaw('id,  ( 6371 * acos( cos( radians(?) ) * cos( radians( map_loc_lat ) )
  * cos( radians( ? ) - radians(?) ) + sin( radians(?) ) 
  * sin( radians( map_loc_lat ) ) ) ) AS distance', )

But I'm confused on how I should specify the question marks to indicate my input latitude and longitude using Eloquent, plus how would I continue chaining on my order by clause and my limits?

图片转代码服务由CSDN问答提供 功能建议

所以我试图在一定距离内找到距离另一个特定点最近的点,我正在使用 这个的半正式公式。 原始查询本身是:

  SELECT id,
(3959 * acos(cos(弧度(input_lat))* cos(弧度(map_loc_lat))* cos(弧度(  map_loc_long) - 弧度(input_long))+ sin(弧度(input_lat))* sin(弧度(map_loc_lat))))
AS距离FROM具有距离的帖子&lt;  25 ORDER BY距离LIMIT 0,20; 
   
 
 

其中 input_lat input_long 是我预定义的坐标 希望我的中心搜索点从头开始, map_loc_lat map_loc_long posts '点。

我正在尝试使用Eloquent的 selectRaw()方法来完成一部分繁重的工作,如下所示: < pre> $ query-&gt; selectRaw('id,(6371 * acos(cos(radians(?))* cos(radians(map_loc_lat)) * cos(弧度(?) - 弧度(?)) + sin(弧度(?)) * sin(弧度(map_loc_lat))))AS距离',)

但是我对我怎么感到困惑 应该使用Eloquent指定问号以指示我的输入纬度和经度,以及如何继续链接我的order by子句和我的限制?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • doulei8475 2014-10-23 12:18
    已采纳

    Used the same sql in one of my projects. Did not find any other solution instead of using DB::raw() and select()

    Here's example :

     $distance = Input::get('distance', 0.1);
     $lat = Input::get('latitude');
     $lng = Input::get('longitude');
     $select="drops.*,(6371 * acos( cos( radians({$lat}) ) * cos( radians( drops.latitude ) ) * cos( radians( drops.longitude ) - radians({$lng}) ) + sin( radians({$lat}) ) * sin( radians(drops.latitude) ) )) AS distance";
     $drops_query = DB::table('drops')->select(DB::raw($select))
                        ->addSelect('users.login as username')
                        ->leftJoin('users', 'users.id', '=', 'drops.user_in')
                        ->where('drops.latitude','<>','')
                        ->where('drops.longitude','<>','');
     $drops_query = $drops_query->having('distance', '<=', $distance)->orderBy('distance','DESC');
     $drops = $drops_query->take($limit)->get();
    

    This approach let me append to this complex query some additional filtering, pagination and etc.

    打赏 评论

相关推荐 更多相似问题