Laravel 5.6 Api - 搜索、排序和过滤数据列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/51555959/
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
Laravel 5.6 Api - Search,sort and filter on list of data
提问by Zammuuz
Hi I am developing a rest api endpoint for retrieving paginated list of users. In the frontend, there are options to search with all the listed columns, sort by all columns and filter by name, status and created date.
嗨，我正在开发一个用于检索分页用户列表的 rest api 端点。在前端，可以选择搜索所有列出的列、按所有列排序并按名称、状态和创建日期过滤。
So far I have created a repository and local scopes in user model for search, sort and filter. This is my code so far. I am confused with the filter option. Since a user a call filter with all the three options. How to pass those values in api in most optimised way?
到目前为止，我已经在用户模型中创建了一个存储库和本地范围，用于搜索、排序和过滤。到目前为止，这是我的代码。我对过滤器选项感到困惑。由于用户具有所有三个选项的呼叫过滤器。如何以最优化的方式在 api 中传递这些值？
Controller:
控制器：
public function index(Request $request)
{
$this->userRepository->getAllUsers($request);
}
Repository function:
存储库功能：
public function getAllUsers($request)
{
// Search Parameter
isset($request->q)? $q = $request->q: $q = null;
// Sort Parameter
if ( isset($request->sortby) && (isset($request->direction)) ) {
$sort[$request->sortby] = $request-> direction;
}
return User::where('type','=','student')
->ofSearch($q)
->ofSort($sort)
->paginate($per_page)
}
Model:
模型：
public function scopeOfSearch($query, $q)
{
if ( $q ) {
$query->orWhere('name', 'LIKE', '%' . $q . '%')
->orWhere('school', 'LIKE', '%' . $q . '%')
->orWhere('email', 'LIKE', '%' . $q . '%')
->orWhere('phone', 'LIKE', '%' . $q . '%')
->orWhere('class', 'LIKE', '%' . $q . '%');
}
return $query;
}
public function scopeOfSort($query, $sort = [])
{
if ( ! empty($sort) ) {
foreach ( $sort as $column => $direction ) {
$query->orderBy($column, $direction);
}
}
else {
$query->orderBy('users.name');
}
return $query;
}
采纳答案by Zammuuz
Anyways I fixed it my creating another post endpoint which will send all the filters with its value. I am not sure if this is the correct way but now I can think of only like this.
无论如何，我修复了它，我创建了另一个 post 端点，它将发送所有过滤器及其值。我不确定这是否是正确的方法，但现在我只能这样想。
Update
更新
Hi guys, i had implemented the filter by following the below tutorial.
大家好，我已经按照以下教程实现了过滤器。
https://m.dotdev.co/writing-advanced-eloquent-search-query-filters-de8b6c2598db
https://m.dotdev.co/writing-advanced-eloquent-search-query-filters-de8b6c2598db
回答by Fellipe Sanches
An easy solution for who needs sort and filter DB data using a Laravel API (tested in Laravel 5.x and 6.1).
对于需要使用 Laravel API（在 Laravel 5.x 和 6.1 中测试）对数据库数据进行排序和过滤的人来说，这是一个简单的解决方案。
First: Create your table in DB. You can do it manually for test proposital, but I recommend follow this: https://laravel.com/docs/5.7/migrations
第一：在数据库中创建你的表。您可以为测试提案手动执行此操作，但我建议您遵循以下步骤：https: //laravel.com/docs/5.7/migrations
In that exemple the table was named 'my_task';
在那个例子中，表被命名为“my_task”；
Then create your Model typing in the root Laravel directory:
然后在 Laravel 根目录中创建你的模型：
php artisan make:model MyTask(return: Model created successfully.)
php artisan make:model MyTask（返回：模型创建成功。）
The model will be created inside app directory.
该模型将在 app 目录中创建。
Next, create your Controller typing in the root Laravel directory:
接下来，在 Laravel 根目录中创建您的控制器：
php artisan make:controller MyTaskController(return: Controller created successfully.)
php artisan make:controller MyTaskController（返回：控制器创建成功。）
The controller will be created inside app/Http/Controllers/After, insert the code below inside the controller MyTaskController.phpfile:
将在控制器内部创建app/Http/Controllers/之后，在控制器MyTaskController.php文件中插入以下代码：
<?php
namespace App\Http\Controllers;
use App\MyTask;
use DB;
use Illuminate\Http\Request;
class MyTaskController extends Controller
{
public function filter($parameter)
{
$filter = DB::table('my_task')
->where('field123', '=', $parameter)
->get();
return $filter;
}
}
Lastly:
Add a route pointing to your controller in the routes/api.phpfile:
最后：在routes/api.php文件中添加指向控制器的路由：
Route::get("my-task/{parameter}", "MyTaskController@filter");
Test your endpoint using a browser or the Postman:
使用浏览器或 Postman 测试您的端点：
http://127.0.0.1/api/my-task/value
In the url above "value" is the value you want to look for in the database my-task table.
在上面的 url 中，“value”是您要在数据库 my-task 表中查找的值。
Ps: You can create new routes poiting to other functions inside the same controller to have others types of manipulations, as 'sort'.
Ps：您可以创建指向同一控制器内其他功能的新路由，以进行其他类型的操作，例如“排序”。
回答by lucas
I'll change your repository's code this way:
我将通过这种方式更改您的存储库代码：
public function getAllUsers($request)
{
// Set query builder
$qb = User::query();
if($request->has('q')){
$qb->ofSearch($q);
}
if($request->has('sortby')){
//Handle default parameter of get with second argument
$qb->orderBy($request->get('sortBy'), $request->get('direction', 'ASC'));
}
return $qb->paginate();
}
Request::get method handle isset checks for you!
Request::get 方法为您处理 isset 检查！
