如何在 Laravel5 中使用原始 sql 分页?

2022-01-04 00:00:00 php laravel laravel-5 pagination

这是我的控制器代码:

$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 ) );

如何向此代码添加分页以构建我的 Restful api?

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

iOS 或 android 会发送next page"参数，如何使用和查找下一段数据?

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() 是来自 IlluminateDatabaseQueryBuilder 类的方法.

$result here will have the array type and paginate() is the method from the IlluminateDatabaseQueryBuilder 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.

相关文章