CODEWITHSUNDEEP
phplaraveleloquent
jreikes
jreikes

Reputation: 714

How do I make a Laravel Eloquent search query sort results by the value returned from a class method?

I'm building some search capabilities into a Laravel 5.2 application. In my model, an Event has a Location, which is comprised of a latitude and longitude value. The Location class has a method for calculating distance $this->distanceTo($that).

I want to be able to search my events by a variety of criteria (keywords, category, etc.), but then order the results by distance to a specified point. So I have a route that looks something like this:

$query = App\Event::query();

if (Input::has('keyword')) {
    $query = $query->where('name', 'like', '%'.Input::get('keyword').'%');
}

if (Input::has('category')) {
    $query = $query->where('category_id', Input::get('category'));
}

$events = $query->get();

It seems to me like I need to run this query and then perform sorting on the collection it returns. But it would probably perform better if there was a way to incorporate the sorting into the query itself. Is there a way to do that?

If not, then I still need to figure out how to perform the sorting on the collection that gets returned. I know there's a sortBy() method available for a collection, but I'm not sure how to put this together when I need to sort by $eventFromCollection->location()->distanceTo($searchLocation). Any guidance on this?

Upvotes: 0

Views: 1293

Answers (1)

Angad Dubey
Angad Dubey

Reputation: 5452

I built a similar feature and will share my experience.

There are a couple of different ways you can do this:

  1. Do the calculation in SQL. This will require using the Haversine formula. Here is an SQL Example . You can find many examples, search for "Geo distance sql"
  2. You can do it using PHP ( see below )
  3. Googles Distance Matrix API. ( Use this if speed and performance is not a concern and you are ready to pay incase of higher queries per day )
  4. Using a Hosted Search as a service that has geo distance built in. ( see below )

I will ignore #1 and #3 as you can easily find "How to's" for those.

2. Using PHP:

You will have to sort the results after the sql operation. You can use something like this:

DO NOT USE IN PRODUCTION WITHOUT PROPER

VALIDATION OR ERROR HANDLING

public function distance($lat1, $lon1, $lat2, $lon2, $unit = 'K') {

    $theta = $lon1 - $lon2;

    $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));

    $dist = acos($dist);

    $dist = rad2deg($dist);

    $miles = $dist * 60 * 1.1515;

    $unit = strtoupper($unit);

    if ($unit == "K") {

        return round(($miles * 1.609344), 2);

    } else if ($unit == "N") {

        return round(($miles * 0.8684), 2);

    } else {

        return round($miles, 2);

    }


}

This will give you the distance between two sets of coordinates. Iterate through your results from DB and sort them using this.

2. Using Hosted search service:

Algolia is a very good solution for "Search" in general. There is a Laravel package available for it that makes it easy to implement, and they have built-in Geo search feature.

I use a combination of 2, 3 and 4 based on the requirement.

Upvotes: 1

Related Questions