minortechnologies's avatar

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.

0 likes
0 replies

Please or to participate in this conversation.