Query for Google Map

posted 10 years ago

Hi guys, I'm new to Laravel and enjoy to play with it. But I stuck almost 2 hours on using query builder to generate a correct MySQL query for my application which using google map. here is the sample I refer: https://developers.google.com/maps/articles/phpsqlsearch_v3#findnearsql

and I would like to generate following query (I try on phpMyAdmin, it's work):

SELECT voucher_id, ( 6371 * acos ( cos( radians(3.076559) ) * cos( radians( users.lat ) ) * cos( radians( users.lng ) - radians(101.644432) ) + sin( radians(3.076559) ) * sin( radians( users.lat ) ) ) 
AS distance
FROM vouchers,users
WHERE vouchers.user_id = users.user_id
HAVING distance < 25
ORDER BY distance LIMIT 0 , 20

I tried several statement, but no one is worked. Any help would be greatly appreciated. =)

Last updated 2 years ago.

pickupman

replied 10 years ago

Solution

Any examples of what is not working? Maybe this might work:

DB::table('vouchers')
    ->select(DB::raw('voucher_id, ( 6371 * acos ( cos( radians(3.076559) ) * cos( radians( users.lat ) ) * cos( radians( users.lng ) - radians(101.644432) ) + sin( radians(3.076559) ) * sin( radians( users.lat ) ) ) AS distance'))
    ->join('users', 'users.user_id', '=','vouchers.user_id')
    ->having('distance', '<', '25')
    ->orderBy('distance')
    ->take(20)
    ->get();

Last updated 2 years ago.

crossrt

replied 10 years ago

My fault! I tried the query almost same like yours yesterday, I try it today again and I only found I accidentally deleted a closing parentheses before 'AS distance'.

Anyway, after add back the closing parentheses, your solution worked very nice! Thank you so much pickupman. =)

Last updated 2 years ago.

Moderators