CODEWITHSUNDEEP
phpmysqllaravel-4
Moe
Moe

Reputation: 470

laravel 4 raw query error

I'm currently trying to construct a raw query to retrieve Nearby, here is my attempt 

$places = DB::select('select * from b_details where(3959 * acos(cos(radians(37)) * cos(radians(lat = ?)) * cos(radians(lng = ?) - radians(-122)) + sin(radians(37)) * sin(radians(lat))))
as distance from b_details having distance < radius = ?  order by distance LIMIT 0 , 20', array($lat, $lng, $radius));

I have also tried: 

    $disctance_select = sprintf(
        "( 3959 * acos( cos( radians(%s) ) " .
        " * cos( radians( lat ) ) " .
        " * cos( radians( lng ) - radians(%s) ) " .
        " + sin( radians(%s) ) * sin( radians( lat ) ) " .
        ") " .
        ") " .
        "AS distance",
        $lat,
        $lng,
        $lat
    );

    return DB::table('b_details')
        ->having('distance', '<', $radius)
        ->orderBy('distance', 'ASC')
        ->get(array('*',$disctance_select));

This keeps producing errors: 

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax

this is the query my trying to copy for use with laravel 4:

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

any help is appreciated, thank you.

Upvotes: 0

Views: 308

Answers (2)

Bastin Robin
Bastin Robin

Reputation: 938

Kindly check this: It really work :)

$result = Listing::select(
            DB::raw("*,
                          ( 6371 * acos( cos( radians(?) ) *
                            cos( radians( lat ) )
                            * cos( radians( lon ) - radians(?)
                            ) + sin( radians(?) ) *
                            sin( radians( lat ) ) )
                          ) AS distance"))
            ->having("distance", "<", $radius)
            ->orderBy("distance")
            ->setBindings([$lat, $lon, $lat])
            ->get();

Upvotes: 1

warspite
warspite

Reputation: 632

You have done a select * followed by a where followed by a calculated column. You need to put the distance calculated column before the where clause.

Why can you not just paste the query you are trying to copy as the first parameter in the Laravel DB:select? I am confused as to why you would change it.

Upvotes: 0

Related Questions