Cómo usar paginate () con una cláusula having () cuando la columna no existe en la tabla

Tengo un caso complicado …

La siguiente consulta a la base de datos no funciona:

DB::table('posts') ->select('posts.*', DB::raw($haversineSQL . ' as distance')) ->having('distance', 'paginate(10); 

Falla con el mensaje: la distancia de la columna no existe.

El error ocurre cuando paginate () intenta contar los registros con

 select count(*) as aggregate from {query without the column names} 

A medida que se eliminan los nombres de las columnas, no se conoce la distancia y se genera una excepción.

¿Alguien tiene un trabajo para poder usar la paginación en este caso?

Gracias

Esto es un problema con el generador de consultas ya que todas las selecciones se descartan cuando se realiza una llamada agregada (como count(*) ). La solución make-do por ahora es construir el pagniator manualmente.

 $query = DB::table('posts') ->select(DB::raw('(c1 - c2) as distance')) ->having('distance', '<=', 5); $perPage = 10; $curPage = Paginator::getCurrentPage(); // reads the query string, defaults to 1 // clone the query to make 100% sure we don't have any overwriting $itemQuery = clone $query; $itemQuery->addSelect('posts.*'); // this does the sql limit/offset needed to get the correct subset of items $items = $itemQuery->forPage($curPage, $perPage)->get(); // manually run a query to select the total item count // use addSelect instead of select to append $totalResult = $query->addSelect(DB::raw('count(*) as count'))->get(); $totalItems = $totalResult[0]->count; // make the paginator, which is the same as returned from paginate() // all() will return an array of models from the collection. $paginatedItems = Paginator::make($items->all(), $totalItems, $perPage); 

Probado con el siguiente esquema usando MySQL:

 Schema::create('posts', function($t) { $t->increments('id'); $t->integer('c1'); $t->integer('c2'); }); for ($i=0; $i < 100; $i++) { DB::table('posts')->insert([ 'c1' => rand(0, 10), 'c2' => rand(0, 10), ]); } 

Usando Eloquent, sé que puedes pasar columnas al paginador, algo como esto:

 Post::having('distance','<=', $distance) ->paginate(10, array('*', DB::raw($haversineSQL . ' as distance'))); 

No estoy seguro de si funciona sin Eloquent, pero podría intentarlo.

Puede usar la paginación manual como comportarse de manera peculiar con la clase de paginación.

 $posts = DB::table('posts') ->select('posts.*', DB::raw($haversineSQL . ' as distance')) ->having('distance', '<=', $distance) ->get(); // Items per page $perPage = 10; $totalItems = count($posts); $totalPages = ceil($totalItems / $perPage); $page = Input::get('page', 1); if ($page > $totalPages or $page < 1) { $page = 1; } $offset = ($page * $perPage) - $perPage; $posts = array_slice($posts, $offset, $perPage); $posts = Paginator::make($posts, $totalItems, $perPage); dd($posts); 

Puede calcular la distancia en la parte WHERE :

 DB::table('posts') ->whereRaw($haversineSQL . '<= ?', [$distance]) ->paginate(10); 

Si necesita el valor de distance en su aplicación, tendrá que calcularlo dos veces:

 DB::table('posts') ->select('posts.*', DB::raw($haversineSQL . ' as distance')) ->whereRaw($haversineSQL . '<= ?', [$distance]) ->paginate(10); 

Este es el scope implementa una búsqueda de fórmula Haversine , con optimización adicional para la velocidad, que se documenta aquí .

Ojalá hubiera una manera más toSql() obtener SQL sin toSql() objeto de consulta, pero desafortunadamente toSql() devuelve SQL antes de que los marcadores de posición hayan sido sustituidos, así que confié en varias *Raw llamadas en *Raw . No es tan malo, pero desearía que fuera más limpio.

El código asume que tiene columnas lat y lng en su tabla.

 const DISTANCE_UNIT_KILOMETERS = 111.045; const DISTANCE_UNIT_MILES = 69.0; /** * @param $query * @param $lat * @param $lng * @param $radius numeric * @param $units string|['K', 'M'] */ public function scopeNearLatLng($query, $lat, $lng, $radius = 10, $units = 'K') { $distanceUnit = $this->distanceUnit($units); if (!(is_numeric($lat) && $lat >= -90 && $lat <= 90)) { throw new Exception("Latitude must be between -90 and 90 degrees."); } if (!(is_numeric($lng) && $lng >= -180 && $lng <= 180)) { throw new Exception("Longitude must be between -180 and 180 degrees."); } $haversine = sprintf('*, (%f * DEGREES(ACOS(COS(RADIANS(%f)) * COS(RADIANS(lat)) * COS(RADIANS(%f - lng)) + SIN(RADIANS(%f)) * SIN(RADIANS(lat))))) AS distance', $distanceUnit, $lat, $lng, $lat ); $subselect = clone $query; $subselect ->selectRaw(DB::raw($haversine)); // Optimize the query, see details here: // http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/ $latDistance = $radius / $distanceUnit; $latNorthBoundary = $lat - $latDistance; $latSouthBoundary = $lat + $latDistance; $subselect->whereRaw(sprintf("lat BETWEEN %f AND %f", $latNorthBoundary, $latSouthBoundary)); $lngDistance = $radius / ($distanceUnit * cos(deg2rad($lat))); $lngEastBoundary = $lng - $lngDistance; $lngWestBoundary = $lng + $lngDistance; $subselect->whereRaw(sprintf("lng BETWEEN %f AND %f", $lngEastBoundary, $lngWestBoundary)); $query ->from(DB::raw('(' . $subselect->toSql() . ') as d')) ->where('distance', '<=', $radius); } /** * @param $units */ private function distanceUnit($units = 'K') { if ($units == 'K') { return static::DISTANCE_UNIT_KILOMETERS; } elseif ($units == 'M') { return static::DISTANCE_UNIT_MILES; } else { throw new Exception("Unknown distance unit measure '$units'."); } } 

Esto se puede usar como tal:

  $places->NearLatLng($lat, $lng, $radius, $units); $places->orderBy('distance'); 

El SQL generado, se verá aproximadamente así:

 select * from ( select *, ( '111.045' * DEGREES( ACOS( COS( RADIANS('45.5088') ) * COS( RADIANS(lat) ) * COS( RADIANS('-73.5878' - lng) ) + SIN( RADIANS('45.5088') ) * SIN( RADIANS(lat) ) ) ) ) AS distance from `places` where lat BETWEEN 45.418746 AND 45.598854 and lng BETWEEN -73.716301 AND -73.459299 ) as d where `distance` <= 10 order by `distance` asc 

Esta no es una respuesta satisfactoria, pero si solo necesita mostrar enlaces simples “Siguiente” y “Anterior” en su vista de paginación, puede usar el método simple de simplePaginate . Realizará una consulta más eficiente y no se bloqueará si usa having .

 DB::table('posts') ->select('posts.*', DB::raw($haversineSQL . ' as distance')) ->having('distance', '<=', $distance) ->simplePaginate(10);