How to convert this query to laravel

Question

I have a bad time converting this subquery to laravel. Can someone help me convert this long query

SELECT 
    rescue_units.id as 'rescue_units_id', 
    name, 
    address, 
    lat, 
    lng, 
    email, 
    type, 
    status, 
    ( 6371 * acos( cos( radians(10.3099568) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(123.8934193) ) + sin( radians(10.3099568) ) * sin( radians( lat ) ) ) ) AS distance
FROM rescue_units 
WHERE rescue_units.id = ANY (
    SELECT ru_id 
    FROM ru_ec 
    WHERE ec_id = (
        SELECT id 
        FROM emergency_codes 
        WHERE ID = 6
    )
) 
AND 
status = 1 
order by distance

EDIT: This is what I have tried so far.

$markersByRadius = RescueUnit::select(
               DB::raw("rescue_units.id as 'rescue_units_id', name, address, lat, lng, email, type, status,
                    ( 6371 * acos( cos( radians(?) ) *  cos( radians( lat ) ) * cos( radians( lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( lat ) ) ) ) AS distance
                    FROM rescue_units WHERE rescue_units.id = ANY (SELECT ru_id FROM ru_ec WHERE ec_id = (SELECT id FROM emergency_codes WHERE ID = ?)) AND status = 1 order by distance"))
               ->take($limit)
               ->setBindings([$lat, $lng, $lat, $ec_id])
               ->get();

        return $markersByRadius;

And this is the error:

SQLSTATE[42000]: 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 'WHERE rescue_units.id = ANY (SELECT ru_id FROM ru_ec WHERE ec_id = (SELECT id FR' at line 3 (SQL: select rescue_units.id as 'rescue_units_id', name, address, lat, lng, email, type, status, ( 6371 * acos( cos( radians(10.3099568) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(123.8934193) ) + sin( radians(10.3099568) ) * sin( radians( lat ) ) ) ) AS distance WHERE rescue_units.id = ANY (SELECT ru_id FROM ru_ec WHERE ec_id = (SELECT id FROM emergency_codes WHERE ID = 6)) AND status = 1 from `rescue_units` order by `distance` asc limit 3)

Please post what you've tried so far. – Joel Hinz Feb 09 '15 at 11:52 — Joel Hinz, Feb 09 '15 at 11:52
@JoelHinz here please check edit... – user352156 Feb 09 '15 at 11:57 — user352156, Feb 09 '15 at 11:57
What doesn't work, what are the errors? – Matheno Feb 09 '15 at 11:57 — Matheno, Feb 09 '15 at 11:57
@MHakvoort check the edit again – user352156 Feb 09 '15 at 12:19 — user352156, Feb 09 '15 at 12:19

score 0 · Answer 1 · edited May 23 '17 at 12:27

Please check this haven't tested but it should work. only part you are doing wrong was sub query.

   $markersByRadius = RescueUnit::select(
               DB::raw("rescue_units.id as 'rescue_units_id', name, address, lat, lng, email, type, status,
                    ( 6371 * acos( cos( radians(?) ) *  cos( radians( lat ) ) * cos( radians( lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( lat ) ) ) ) AS distance"))
                    ->where('rescue_units.id', function($query)
                            {
                                $query->select(DB::raw('ru_id'))
                                      ->from('ru_ec')
                                      ->where('ru_ec.ec_id', function($query)
                            {
                                $query->select(DB::raw('id'))
                                      ->from('emergency_codes')
                                      ->where('ID = ?')
                                      ->where('status','1')
                                      ->orderBy('distance', 'ASC');
                            }));
                            })

               ->take($limit)
               ->setBindings([$lat, $lng, $lat, $ec_id])
               ->get();

How to convert this query to laravel

1 Answers1