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

pagination laravel 5 example

Question

Tengo un caso complicado ...

La siguiente consulta de la base de datos no funciona:

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

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

El error se produce 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 columna, no se conoce la distancia y se genera una excepción.

¿Alguien tiene una solución para poder usar la paginación en este caso?

Gracias

Answer 1

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 simplePaginate . Realizará una consulta más eficiente y no se bloqueará si usas having .

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

Answer 2

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

Desearía que hubiera una forma más limpia de obtener SQL sin procesar del objeto de consulta, pero desafortunadamente toSql() devuelve SQL antes de que los marcadores de posición hayan sido sustituidos, por lo que confié en varias *Raw llamadas sin *Raw . No está tan mal, pero me gustaría que estuviera más limpio.

El código asume que tienes columnas lat y lng en tu 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 puede ser usado 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

Answer 3

Esto es algo así como 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 para hacer 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),
    ]);
}

Answer 4

Hay una mejor manera y también funciona con enlaces:

        $curPage = /Illuminate/Pagination/Paginator::resolveCurrentPage();
        $total = $model->get()->count();
        $items = $model->forPage($curPage, $showPerPag)->get();
        $paginated = new /Illuminate/Pagination/LengthAwarePaginator($items, $total, $showPerPage, $curPage, [''path'' => request()->url(), ''query'' => request()->query()]);

Answer 5

Puede utilizar la paginación manual como comportamiento 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);

Answer 6

Puedes calcular la distancia en la parte WHERE :

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

Si necesita el valor de la 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);

Answer 7

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 puedes intentarlo.