如何在Laravel5中使用原始SQL分页? [英] How to use raw sql Pagination in Laravel5?

问题描述

这是我的控制器代码:

$sql = "SELECT *,earth_distance(ll_to_earth(team.lat, team.lng), ll_to_earth(23.1215939329,113.3096030895)) AS distance FROM team where earth_box(ll_to_earth(23.1215939329,113.3096030895),1000) @> ll_to_earth(team.lat, team.lng); ";
$result = DB::select( \DB::raw( $sql ) );

如何在此代码中添加分页以构建我的静态API?

How can I add pagination to this code to build my restful api?

iOS或android将发送下一页"参数，如何使用它并查找下一部分数据?

iOS or android will send the "next page" parameter, how to use it and find the next section data?

推荐答案

据我所知您无法对原始查询进行分页，原因如下:

As far as I know you can't paginate raw query, here's why:

$result = DB::select($sql); 

$result在这里将具有数组类型，而paginate()是Illuminate\Database\Query\Builder类的方法.

$result here will have the array type and paginate() is the method from the Illuminate\Database\Query\Builder class.

您的案件可以通过以下方式执行:

Your case can be performed this way:

$items = DB::table('team')   
    ->selectRaw('SELECT *,earth_distance(ll_to_earth(team.lat, team.lng), ll_to_earth(23.1215939329,113.3096030895)) AS distance')
    ->whereRaw('earth_box(ll_to_earth(23.1215939329,113.3096030895),1000) @> ll_to_earth(team.lat, team.lng)')
    ->paginate(10);

foreach($items as $item) {
    echo $item->distance;
}

如您所见，将原始查询分离为selectRaw()和whereRaw()方法所需的工作量很小.

As you can see minimal effort is needed here to separate raw query to selectRaw() and whereRaw() methods.

这篇关于如何在Laravel5中使用原始SQL分页?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！