如何在带有原始查询的 Laravel 5 中使用分页
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30321497/
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 pagination in laravel 5 with Raw query
提问by Erusso87
I have a simple question and I didn′t find what I need.
我有一个简单的问题，但我没有找到我需要的。
I need to calculate the distance between t2 geocode points for a list of Stores. I also need it to be paginated for a WebService.
我需要计算商店列表的 t2 地理编码点之间的距离。我还需要为 WebService 对它进行分页。
This works, but there is no distance in the result:
这有效，但结果中没有距离：
public function stores(){
return Store::paginate(10);
}
And the result is:
结果是：
{
total: 4661,
per_page: 10,
current_page: 6,
last_page: 467,
next_page_url: "WS_URL/stores/?page=7",
prev_page_url: "WS_URL/stores/?page=5", from: 51,
to: 60,
data: [ {
id: "51",
name: "Sprouts",
.
.
lng: "-118.359688",
lat: "33.808281",
country: "usa"
},
.
.
.
]}
But I need this code working:
但我需要这段代码工作：
public function stores(){
return DB::table('stores')
->selectRaw(' *, distance(lat, ?, lng, ?) as distance ')
->setBindings([ 41.123401,1.2409893])
->orderBy('distance')
->paginate($this->limit);
}
And this is the result:
这是结果：
{total: 0,
per_page: 10,
current_page: 1,
last_page: 0,
next_page_url: null,
prev_page_url: null,
from: 1,
to: 10,
data: [{
id: "3686",
name: "Bon Area",
.
.
lng: "1.602016",
lat: "41.266823",
distance: "0.15091"
},
.
.
.
]
}
I need the next_page_urland prev_page_url
我需要next_page_url和prev_page_url
Any ideas?
有任何想法吗？
采纳答案by maxwilms
Use the selectRawmethod on the Eloquent model.
selectRaw在 Eloquent 模型上使用该方法。
Store::selectRaw('*, distance(lat, ?, lng, ?) as distance', [$lat, $lon])
->orderBy('distance')
->paginate(10);
In that case Laravel asks the database for the amount of rows (using select count(*) as aggregate from stores) which saves your RAM.
在这种情况下，Laravel 会向数据库询问select count(*) as aggregate from stores可以节省 RAM的行数（使用 ）。
回答by Erusso87
Ok, I got a solution! I don′t like it because I must to load all the rows in RAM and then use the manual paginator.
好的，我找到了解决方案！我不喜欢它，因为我必须加载 RAM 中的所有行，然后使用手动分页器。
public function stores($lat, $lon){
$stores = DB::table('stores')
->selectRaw(' *, distance(lat, ?, lng, ?) as distance ')
->setBindings([$lat,$lon])
->orderBy('distance')
->get();
$result_p = new Paginator($stores, $this->limit, Request::input('page'),['path' => Request::url() ]);
return $result_p;
}
After this I was looking more inforamtion and the problem is setBindings([$lat,$lon])
在此之后，我查看了更多信息，问题是setBindings([$lat,$lon])
The latest and Good solution:
最新的好解决方案：
public function stores($lat, $lon){
return $stores = DB::table('stores')
->selectRaw(" *, distance(lat, {$lat}, lng, {$lon}) as distance ")
->orderBy('distance')
->paginate($this->limit);
}
