Erusso87 Erusso87 - 15 days ago 7
PHP Question

How to use pagination in laravel 5 with Raw query

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.

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_url
and
prev_page_url


Any ideas?

Answer

Use the selectRaw method on the Eloquent model.

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.