lundi 25 juillet 2016

Laravel Raw Query with Location, Join, and Pagination

I am using Laravel 5 to build functionality to allow a user to search for specials from vendors that are near their location. I have a table that holds vendors with their locations and another table that holds specials that vendors are offering. The specials table has a vendor_id column to link to the vendors table.

vendors (id, name, latitude, longitude) - some fields omitted
specials (id, name, description, vendor_id) - some fields omitted

I am trying to run a query that uses the latitude and longitude from the vendors table and also joins the specials table to get all information for a special including the vendor name and id (so that I can link to the vendor later). Below is my controller that I have so far.

public function postSearch(Request $request)
{
    DB::enableQueryLog();

    $specials = $this->special->with('vendor')->newQuery();

    if ($request->has('zip')) {
        $response = \Geocode::make()->address($request->input('zip'));
        $latitude = $response->latitude();
        $longitude = $response->longitude();
    } else {
        $latitude = $request->input('latitude');
        $longitude = $request->input('longitude');
    }

    $radius = ($request->input('radius') * 1.1);

    if ($request->has('category')) {
        $specials->where('category_id', $request->input('category'));
    }

    $specials->select(DB::raw("*, (3959 * acos(cos(radians(" . $latitude . ")) * cos(radians(vendors.latitude)) * cos(radians(vendors.longitude) - radians(" . $longitude . ")) + sin(radians(" . $latitude . ")) * sin(radians(vendors.latitude)))) AS distance"))->having('distance', '<', $radius);

    $now = date('Y-m-d H:i:s');
    $specials->where('end_date', '>', $now);

    $discounts = $specials->orderBy('distance')->get();

    dd(DB::getQueryLog());

    $body = View::make('main.specials.table', ['discounts' => $discounts])->render();

    return response()->json(['discounts' => $discounts, 'body' => $body], 200);
}

The query log is not returning anything for me to analyze. On top of joining the vendors table with the specials table I also need to paginate the results. I'm drawing blanks on how to accomplish this. Any help is appreciated.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire