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

Question

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!!

can you not just do `$result = collect($pubsFiltered);` to make it a collection? — Joe, May 18 '18 at 08:59

score 1 · Answer 1 · answered May 18 '18 at 08:58

1

$pubs = Pubs::hydrate($pubsFiltered)

answered May 18 '18 at 08:58

Quezler

2,376
14
29

1

Some additional info rather than a one liner of code would help this answer massively, and would avoid it getting flagged for deletion (as this one has!). – Rob Quincey May 18 '18 at 11:59
I agree with Rob Quincey. Edit this post or it's going to be deleted. – RubioRic May 18 '18 at 12:47

score 0 · Answer 2 · answered May 18 '18 at 09:15

For now, I am using this query to obtain nearby records:

public function scopeNearby($query, $lat, $long, $distance = 10)
{
    if($lat AND $long) {
        // 3959 for miles, 6371 for kilometers
        $distanceQuery = '(6371 * acos(cos(radians(?)) * cos(radians(geo_lat)) * ' . 
            'cos(radians(geo_long) - radians(?)) + sin(radians(?)) ' .
            '* sin(radians(geo_lat))))';

        return $query->select('*', DB::raw("{$distanceQuery} as distance"))
                ->addBinding([$lat, $long, $lat], "select")
                ->whereNotNull('geo_lat')
                ->whereNotNull('geo_long')
                ->whereRaw("{$distanceQuery} < ?")
                ->addBinding([$lat, $long, $lat, $distance], "where")
                ->orderBy('distance', "ASC");
    }
}

You may notice that I have used the $distanceQuery for both SELECT and WHERE clauses. I am currently having issues when this scope is used alongside with the paginate() method. This might need some refactoring, but for now you can do the same.

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

2 Answers2