README

基于 "Laravel Query Builder" 的 QueryFilter

如果我们需要以“安全”模式构建查询字符串怎么办？🤔

或者我们有多个路由来获取模型的兄弟列表？🙇

例如

/**
 * @property $id int
 * @property $firstname string
 * @property $lastname string
 * @property $role string|null
 * @property $created_at Carbon
 */
class User {}

// ...

Route::get('/users', [UserController::class, 'users']);
Route::get('/users/superusers', [UserController::class, 'superusers']);
Route::get('/users/all', [UserController::class, 'all']);

// ...

class UserController {
    public function users() {
        return User::query()->whereNull('role')->get();
    }
    
    public function superusers() {
        return User::query()->whereNotNull('role')->get();
    }
    
    public function all() {
        return User::query()->orderBy('username')->get();
    }
}

而不是这样做，我们可以通过查询参数来构建查询！🙋

此外，我们还可以更改分页页码和限制，查询排序方向或使用筛选条件。🧐

让我们为模型 User 创建 DataProvider

use Papalapa\Laravel\QueryFilter\BaseDataProvider;

final class UserDataProvider extends BaseDataProvider
{
    /**
     * Default sorting columns, when other not set 
     */
    protected array $defaultSort = [
        'id' => 'asc',
    ];
    
    /**
     * Final sorting columns, which use always 
     */
    protected array $finalSort = [
        'created_at' => 'desc',
    ];
    
    /**
     * Safe-attributes to use in filtration 
     */
    protected array $allowedFilter = [
        'name' => ['lastname', 'firstname'], // alias of two columns
        'role',
    ];

    /**
     *  Safe-attributes to use in sorting
     */
    protected array $allowedSort = [
        'name' => ['lastname', 'firstname'], // alias of two columns
        'datetime' => 'created_at',
    ];

    protected function makeBuilder() : EloquentBuilder
    {
        return User::query()
            ->select([
                'id',
                'lastname',
                'firstname',                
                'role',
            ]);
    }
}

好，现在我们准备好重构我们的路由和控制器

Route::get('/users', [UserController::class, 'users']);

class UserController {
    public function users(UserDataProvider $dataProvider) {
        return $dataProvider->paginate();
    }
}

这就是全部！💥 但它是如何工作的？❓

我们的新请求必须包含特殊查询属性来控制查询构建：⚡

https://domain.local/users
    ?_filter={"name": "^John", "or": [ {"role": "admin"}, {"role": "manager"} ], "and": [ {"datetime": ">=2021-01-01"}, {"datetime": "<=2021-02-01"} ]}
    &_sort=datetime,-name
    &_order=desc
    &_page=2
    &_limit=50

构建的 SQL 查询将是：✨

SELECT id, firstname, lastname, role
FROM users
WHERE
      ((lastname LIKE 'John%') OR (firstname LIKE 'John%'))
      AND
      ((role = 'admin') OR (role = 'manager'))
      AND
      ((created_at >= '2021-01-01') AND (created_at <= '2021-02-01'))
ORDER BY created_at ASC, lastname DESC, firstname DESC, created_at DESC 
LIMIT 50 OFFSET 50

附加条件

<> >= != <= > = <

! === NOT LIKE '%xxx%'
* === LIKE '%xxx%'
^ === LIKE '%xxx'
$ === LIKE 'xxx%'

关于 NULL 的处理

{"role": null} === role IS NULL
{"role": "~"} === role IS NOT NULL
or
{"is null": "role"}
{"is not null": "role"}

很简单！🙂