我有一个简单的问题,我没有找到我需要的。
我需要计算商店列表的t2地理编码点之间的距离。我还需要为WebService分页。
这可以工作,但是结果中没有距离:
public function stores(){
return Store::paginate(10);
}
结果是:
{
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"
},
.
.
.
]}
但是我需要这个代码工作:
public function stores(){
return DB::table('stores')
->selectRaw(' *, distance(lat, ?, lng, ?) as distance ')
->setBindings([ 41.123401,1.2409893])
->orderBy('distance')
->paginate($this->limit);
}
结果如下:
{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"
},
.
.
.
]
}
我需要next_page_url
和prev_page_url
任何想法?
在Eloquent模型上使用selectRaw
方法
Store::selectRaw('*, distance(lat, ?, lng, ?) as distance', [$lat, $lon])
->orderBy('distance')
->paginate(10);
在这种情况下,Laravel向数据库询问行数(使用select count(*) as aggregate from stores
),这节省了您的RAM。
好了,我有办法了!我不喜欢它,因为我必须在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;
}
在此之后,我正在寻找更多的信息,问题是setBindings([$lat,$lon])
最新的解决方案:
public function stores($lat, $lon){
return $stores = DB::table('stores')
->selectRaw(" *, distance(lat, {$lat}, lng, {$lon}) as distance ")
->orderBy('distance')
->paginate($this->limit);
}