laravel 当表中不存在列时，如何将 paginate() 与 have() 子句一起使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19349397/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use paginate() with a having() clause when column does not exist in table
提问by YaFred
I have a tricky case ...
我有一个棘手的案例...
Following database query does not work:
以下数据库查询不起作用：
DB::table('posts')
->select('posts.*', DB::raw($haversineSQL . ' as distance'))
->having('distance', '<=', $distance)
->paginate(10);
It fails with message: column distance does not exist.
它失败并显示消息：列距离不存在。
The error occurs when paginate() tries to count the records with
当 paginate() 尝试计算记录时发生错误
select count(*) as aggregate from {query without the column names}
As the column names are stripped, distance is not known and an exception is raised.
由于列名被剥离，距离未知并引发异常。
Does somebody have a work around to be able to use pagination is this case ?
在这种情况下，有人有办法使用分页吗？
Thanks
谢谢
采纳答案by Jonas Staudenmeir
You can calculate the distance in the WHEREpart:
您可以计算WHERE零件中的距离：
DB::table('posts')
->whereRaw($haversineSQL . '<= ?', [$distance])
->paginate(10);
If you need the distancevalue in your application, you'll have to calculate it twice:
如果您需要distance应用程序中的值，则必须计算两次：
DB::table('posts')
->select('posts.*', DB::raw($haversineSQL . ' as distance'))
->whereRaw($haversineSQL . '<= ?', [$distance])
->paginate(10);
回答by Andreas
This is somewhat of a problem with the query builder as all selects are discarded when doing an aggregate call (like count(*)). The make-do solution for now is to construct the pagniator manually.
这对查询构建器来说有点问题，因为在执行聚合调用（如count(*)）时所有选择都被丢弃。现在的解决方案是手动构建pagniator。
$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);
Tested with the following schema using MySQL:
使用 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),
]);
}
回答by Adam
This is not a satisfying answer, but if you only need to display simple "Next" and "Previous" links in your pagination view, you may use the simplePaginatemethod. It will perform a more efficient query and it won't crash if you use having.
这不是一个令人满意的答案，但如果您只需要在分页视图中显示简单的“下一个”和“上一个”链接，您可以使用该simplePaginate方法。它将执行更有效的查询，并且如果您使用having.
DB::table('posts')
->select('posts.*', DB::raw($haversineSQL . ' as distance'))
->having('distance', '<=', $distance)
->simplePaginate(10);
回答by Jaco
Using Eloquent, I know you can pass columns to the paginator, something like this:
使用 Eloquent，我知道您可以将列传递给分页器，如下所示：
Post::having('distance','<=', $distance)
->paginate(10, array('*', DB::raw($haversineSQL . ' as distance')));
Not sure if it works without Eloquent, but you could give it a try.
不确定它是否可以在没有 Eloquent 的情况下工作，但您可以尝试一下。
回答by moltar
This is scope implements a Haversine formulasearch, with additional optimization for speed, which is documented here.
这是范围实现了Haversine 公式搜索，并进行了额外的速度优化，记录在此处。
I wish there was a cleaner way to get raw SQL from the query object, but unfortunately toSql()returns SQL before the placeholders have been substituted, so I relied on several *Rawcalls. It's not too bad, but I wish it was cleaner.
我希望有一种更简洁的方法可以从查询对象中获取原始 SQL，但不幸的是toSql()在占位符被替换之前返回 SQL，所以我依赖了几次*Raw调用。还不错，但我希望它更干净。
The code assumes you have columns latand lngin your table.
该代码假设您的表中有列lat和lng。
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'.");
}
}
This can be used as such:
这可以这样使用：
$places->NearLatLng($lat, $lng, $radius, $units);
$places->orderBy('distance');
The SQL generated, will look approximately like this:
生成的 SQL 大致如下所示：
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
回答by Anam
You can use manual pagination as havingbehaving peculiar with pagination class.
您可以使用手动分页作为having分页类的特殊行为。
$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);
回答by Julio Popócatl
There is a better way and also works with links:
有一个更好的方法，也适用于链接：
$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()]);
