ssquare
3 months ago

How to convert following sql query to laravel query buillder?

Posted 3 months ago by ssquare
$sql = "SELECT id, name, ( 3959 * acos ( cos ( radians(?) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(?) ) + sin ( radians(?) ) * sin( radians( latitude ) ) ) ) AS distance FROM branch HAVING distance < ? ORDER BY distance LIMIT 0 , 20";

$data = \DB::select($sql,[$latitude, $longitude, $latitude, $distance]);


I am trying to get all the branch office which is in the perimeter of a certain distance. The above code is running well with SQL query but I want to try with query builder. Could anyone suggest me its equivalent.

Update:

$query  = \DB::table('branch')
            ->select(
                'id',
                'name',
                \DB::raw('( 3959 * acos ( cos ( radians(?) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(?) ) + sin ( radians(?) ) * sin( radians( latitude ) ) ) ) as distance HAVING distance < ?',$request->distance)
            )
            ->orderBy('distance', 'asc')
            ->offset(0)
            ->limit(20);

        $data = $query->get();

Error

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HAVING distance < ? from rinks order by distance asc limit 20 offset 0' at line 1

Please sign in or create an account to participate in this conversation.