Search code examples
phpmysqllaravelgeolocationxampp

I have a working laravel select query. how do i add filters?

hey guys hows it going? thanks in advance.
I have written a working function in a laravel model which does a geosearch and returns the order in search by distance. It works beautifully.

 public static function searchByDistance($distance, $unit)
{
    //spatial queries
   // $circle_radius = 6371; 
    $circle_radius = 3959; 
    $max_distance = $distance;
    $lat = Auth::user()->getLoc()->latitude;
    $lng = Auth::user()->getLoc()->longitude;
    //get all nearby places
     $nearbyusers = DB::select(
           'SELECT * FROM
                (SELECT users.id, users.country, places.name, places.state_code, places.latitude, places.longitude, (' . $circle_radius . ' * acos(cos(radians(' . $lat . ')) * cos(radians(latitude)) *
                cos(radians(longitude) - radians(' . $lng . ')) +
                sin(radians(' . $lat . ')) * sin(radians(latitude))))
                AS distance
                FROM users
                LEFT JOIN places ON users.suburb=places.name) AS distances
            WHERE distance < ' . $max_distance . '
            ORDER BY distance;
     ');        
   $nearbyusers = (object)$nearbyusers;
    return $nearbyusers;
}

however i have one issue. I want to call the function, return a query and keep being able to add ->where() operations to it until im ready to use get() and return the results.

I cant use where() or get() and i know why. its not in the right format. is there a way someone could help me modify/alter the function so that i could add more filters once i return the $nearbyusers value?

cheers

Solution

  • You're going to want to look at the underlying Fluent query builder system. As it stands, your query is standalone. It's also possible that this particular one may need to stay standalone.

    In order to hook in to Fluent in the way you want, you'll need to pass in the query (this should get auto-resolved), add to it, then return the query. In relation to your current function, that will likely require a fairly different approach than what you have (though what you have is a good model for the query that you want to end up with).

    Have a look at the docs about query scopes, which may be addressing how you're trying to integrate it. Also, have a look at the code itself, such as this one for the JoinClause function, to get an idea of how to structure your code.

    The base elements are that you need to take and return the query object. This means that it can't be a static method (it needs the object instance), and depending on the approach you take, you'll either be returning the passed-in $query object (scopes) or $this (the instance of them model object, itself).

    You'll need to figure out a way to encapsulate the query you have into just the segment you need. It may be something like:

    public function byDistance($distance, $unit) {
    $lat = Auth::user()->getLoc()->latitude;
    $lng = Auth::user()->getLoc()->longitude;

    return $this->join(DB::select('SELECT users.id, users.country, places.name, places.state_code, places.latitude, places.longitude, (' . $circle_radius . ' * acos(cos(radians(' . $lat . ')) * cos(radians(latitude)) *
            cos(radians(longitude) - radians(' . $lng . ')) +
            sin(radians(' . $lat . ')) * sin(radians(latitude)))
            AS distance'), 
         'users.id', 'distance.user_id = users.id')
    ->leftJoin('places', 'users.suburb = places.name')
    ->where('distance', '<', $distance)
    ->orderBy('distance');
}

    Notice the lack of an actual select within the query building part (the nested select notwithstanding), and the lack of any type of actual fetching. What this does is add to the current query, which will then be built when get or other fetch function is finally called. This gives you a discrete segment of a larger query that you can then add to when calling it.