Quadram
1 year ago

How not to use DB in the following query having a subquery

Posted 1 year ago by Quadram

I'm having the following function to calculate distances between two points using Haversine formula:

public static function getByDistance($distance)
{
    $pubsFiltered = DB::select(
    'SELECT * FROM
            (SELECT *, (6371 * acos(cos(radians(40.4169473)) * cos(radians(latitude)) *
            cos(radians(longitude) - radians(-3.7035285)) +
            sin(radians(40.4169473)) * sin(radians(latitude))))
            AS distance
            FROM pubs) AS distances
        WHERE distance < ' . $distance . '
        ORDER BY distance
        ;
    ');

return $pubsFiltered;
}

This is returning an array instead of a collection, that is what I need.

If I change DB to Pub it returns an error because of the subquery where I calculate "distance" using my table "pubs".

How can I change the whole function or simply the subquery to have an instance of my Pub model?...Do I have to use set/declare variables in mysql?

Thanks a lot!!

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