Ostatnio robilem projekt z wyszukiwaniem przez kod pocztowy i natrafilem na problem z paginacja wynikow,
ktore byly otrzymane za pomoca formuly haversine.
W efekcie doszedlem do takiego czegos i chcialbym wiedziec czy jest to dobre z punktu widzenia optymalizacji,
a jak nie to co mozna z tym zrobic.
Kod
App\Models\Business
public function scopeDistance($query, $latitude, $longitude, $radius)
{
$query->getQuery()->orders = [];
return $query->select('*')
->selectRaw("( 3959 * acos( cos( radians($latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians($longitude) ) + sin( radians($latitude) ) * sin(radians(latitude)) ) ) AS distance")
->having('distance', '<=', $radius)
->orderBy('distance');
}
public function scopeDistance($query, $latitude, $longitude, $radius)
{
$query->getQuery()->orders = [];
return $query->select('*')
->selectRaw("( 3959 * acos( cos( radians($latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians($longitude) ) + sin( radians($latitude) ) * sin(radians(latitude)) ) ) AS distance")
->having('distance', '<=', $radius)
->orderBy('distance');
}
Paginator:
Kod
function custom_paginator($builder, $per_page)
{
$path = current_route();
$current_page = \Illuminate\Pagination\Paginator::resolveCurrentPage();
if ( ! isset($builder->getQuery()->columns[1])) $count = $builder->count();
else
{
$query = clone $builder->getQuery();
$query->columns = [ $query->columns[1] ];
$query->orders = null;
$count = array_get(\DB::select("select count(*) as count from ({$query->toSql()}) as haversine", $query->getBindings()), 0)->count;
}
return new \Illuminate\Pagination\LengthAwarePaginator(
$builder->forPage($current_page, $per_page)->get(),
$count, $per_page, null, compact('path')
);
}
{
$path = current_route();
$current_page = \Illuminate\Pagination\Paginator::resolveCurrentPage();
if ( ! isset($builder->getQuery()->columns[1])) $count = $builder->count();
else
{
$query = clone $builder->getQuery();
$query->columns = [ $query->columns[1] ];
$query->orders = null;
$count = array_get(\DB::select("select count(*) as count from ({$query->toSql()}) as haversine", $query->getBindings()), 0)->count;
}
return new \Illuminate\Pagination\LengthAwarePaginator(
$builder->forPage($current_page, $per_page)->get(),
$count, $per_page, null, compact('path')
);
}
Koncowe zapytanie:
Kod
"select count(*) as count from (select ( 3959 * acos( cos( radians(51.5872718) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-0.549759) ) + sin( radians(51.5872718) ) * sin(radians(latitude)) ) ) AS distance from `businesses` having `distance` <= ?) as harversine"
Ogolnie poruszylem ten temat na codereview.stackexchange.com, ale nie wywolal burzy mozgow.
Pomoc mile widziana,
Pozdrawiam.