public function search(Request $request)
{
$params = [];
$query = Ride::select('rides.*', DB::raw('min(geo_distance(route_points.location, POINT(?,?))) as min_dist'));
$params[] = $longitude = $request->input('longitude');
$params[] = $latitude = $request->input('latitude');
$distance = $request->input('distance');
$deltaLong = $distance / abs(cos($latitude) * 69);
$deltaLat = $distance / 69;
$query->whereExists(function ($query) use(&$request, &$params, $deltaLong, $deltaLat, $longitude, $latitude) {
$query->select(DB::raw(1))->from('routes')->whereRaw('rides.head = routes.id');
if ($request->input('startAfter')) {
$query->whereRaw('routes.startTime >= ?');
$params[] = $request->input('startAfter');
}
if ($request->input('endBefore')) {
$query->whereRaw('routes.endTime <= ?');
$params[] = $request->input('endBefore');
}
$query->whereExists(function ($query) use(&$request, &$params, $deltaLong, $deltaLat, $longitude, $latitude) {
$query->select(DB::raw(1))->from('route_points')->whereRaw('routes.id = route_points.route_id')->whereRaw('mbrcontains(linestring(point(?,?),point(?,?)), route_points.location)');
$params[] = $longitude - $deltaLong;
$params[] = $latitude - $deltaLat;
$params[] = $longitude + $deltaLong;
$params[] = $latitude + $deltaLat;
});
})->leftJoin('routes', 'rides.head', '=', 'routes.id')->leftJoin('route_points', 'routes.id', '=', 'route_points.id')->groupBy('rides.id')->orderBy('min_dist', 'asc')->orderBy('routes.endTime', 'asc')->having('min_dist', '<=', DB::raw('?'));
$params[] = $distance;
$query->setBindings($params);
$result = $query->get();
return Response::json(['status' => 'success', 'data' => DbUtil::serializeRides($result)]);
}
