README

安装

要通过 composer 安装，请在终端运行以下命令

composer require "laravie/query-filter"

用法

排序查询

new Laravie\QueryFilter\Orderable(?string $column, string $direction = 'asc', array $config = []);

该类提供了一个简单的接口来处理 Laravel Eloquent/查询构建器的 ORDER BY 查询。

use App\User;
use Laravie\QueryFilter\Orderable;

$query = User::query();

$orderable = new Orderable(
    'name', 'desc'
);

return $orderable->apply($query)->get();

select * from `users` order by `name` desc;

代码将在尝试对查询应用 orderBy() 之前验证列名，这可以防止 SQL 注入，尤其是在列名由用户提供时。

搜索查询

new Laravie\QueryFilter\Searchable(?string $keyword, array $columns = []);

该类提供了一个简单的接口来执行 Laravel Eloquent/查询构建器的 LIKE 查询。

use App\User;
use Laravie\QueryFilter\Searchable;

$query = User::query();

$searchable = new Searchable(
    'crynobone', ['name', 'email']
);

return $searchable->apply($query)->get();

select * from `users` 
where (
    (
        `name` like 'crynobone' 
        or `name` like 'crynobone%'
        or `name` like '%crynobone'
        or `name` like '%crynobone%'
    ) or (
        `email` like 'crynobone' 
        or `email` like 'crynobone%'
        or `email` like '%crynobone'
        or `email` like '%crynobone%'
    )
);

使用通配符搜索

设置特定的 % 或 * 通配符以减少可能的 LIKE 变化。

use App\User;
use Laravie\QueryFilter\Searchable;

$query = User::query();

$searchable = new Searchable(
    'crynobone*gmail', ['name', 'email']
);

return $searchable->apply($query)->get();

select * from `users` 
where (
    (
        `name` like 'crynobone%gmail'
    ) or (
        `email` like 'crynobone%gmail'
    )
);

使用精确通配符搜索

使用 noWildcardSearching() 禁止添加额外的搜索条件。

use App\User;
use Laravie\QueryFilter\Searchable;

$query = User::query();

$searchable = (new Searchable(
    'crynobone@gmail', ['name', 'email']
))->noWildcardSearching();

return $searchable->apply($query)->get();

select * from `users` 
where (
    (
        `name` like 'crynobone@gmail'
    ) or (
        `email` like 'crynobone@gmail'
    )
);

使用 JSON 路径搜索

这将允许您使用不区分大小写的 LIKE 来查询 JSON 路径。

use App\User;
use Laravie\QueryFilter\Searchable;

$query = User::query();

$searchable = new Searchable(
    'Malaysia', ['address->country']
);

return $searchable->apply($query)->get();

select * from `users` 
where (
    (
        lower(json_unquote(json_extract(`meta`, '$."country"'))) like 'malaysia'
        or lower(json_unquote(json_extract(`meta`, '$."country"'))) like 'malaysia%'
        or lower(json_unquote(json_extract(`meta`, '$."country"'))) like '%malaysia'
        or lower(json_unquote(json_extract(`meta`, '$."country"'))) like '%malaysia%'
    )
);

使用关系搜索

这将使得在当前模型及其关系中的搜索变得容易。

use App\User;
use Laravie\QueryFilter\Searchable;

$query = User::query();

$searchable = new Searchable(
    'Administrator', ['name', 'roles.name']
);

return $searchable->apply($query)->get();

select * from `users` 
where (
    (
        `name` like 'Administrator' 
        or `name` like 'Administrator%'
        or `name` like '%Administrator'
        or `name` like '%Administrator%'
    ) or exists (
        select * from `roles` 
        inner join `user_role` 
            on `roles`.`id` = `user_role`.`role_id` 
        where `users`.`id` = `user_role`.`user_id` 
        and (
            `name` like 'Administrator' 
            or `name` like 'Administrator%' 
            or `name` like '%Administrator' 
            or `name` like '%Administrator%'
        )
    )
);

关系搜索只能应用于 Illuminate\Database\Eloquent\Builder，因为它需要通过 whereHas() 查询确保关系的存在。

使用形态关系搜索

您可以使用以下选项使用多态关系搜索

use App\Comment;
use Laravie\QueryFilter\Searchable;
use Laravie\QueryFilter\Filters\MorphRelationSearch;

$query = Comment::query();

$searchable = new Searchable(
    'Administrator', ['name', new MorphRelationSearch('commentable', 'name')]
);

return $searchable->apply($query)->get();

分类查询

new Laravie\QueryFilter\Taxonomy(?string $keyword, array $rules, array $columns = []);

分类总是让开发者创建一组规则来使用 WHERE ... AND 对搜索关键字进行分组。对于任何未分组的关键字，它将通过基于给定的 $columns 的 Laravie\QueryFilter\Searchable 来执行。

use App\User;
use Laravie\QueryFilter\Taxonomy;

$query = User::query();

$taxonomy = new Taxonomy(
    'is:admin email:[email protected]', [
        'email:*' => static function ($query, $value) {
            return $query->where('email', '=', $value);
        },
        'role:[]' => static function ($query, array $value) {
            return $query->whereIn('role', $value);
        },
        'is:admin' => static function ($query) {
            return $query->where('admin', '=', 1);
        },
    ],
);

$taxonomy->apply($query)->get();

select * from `user` 
where `email`='[email protected]'
and `admin`=1;

集成

查询构建器宏

您可以通过将以下代码添加到您的 AppServiceProvider（在 register 方法下）来将 Searchable 集成到数据库或 Eloquent 查询构建器宏中。

<?php

namespace App\Providers;

use Illuminate\Database\Eloquent\Builder as EloquentBuilder;
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Support\Arr;
use Laravie\QueryFilter\Searchable;

class AppServiceProvider extends \Illuminate\Support\ServiceProvider 
{
    /**
     * Register any application services.
     *
     * This service provider is a great spot to register your various container
     * bindings with the application. As you can see, we are registering our
     * "Registrar" implementation here. You can add your own bindings too!
     *
     * @return void
     */
    public function register()
    {
        QueryBuilder::macro('whereLike', static function ($attributes, string $searchTerm) {
            return (new Searchable($searchTerm, Arr::wrap($attributes)))->apply($this);
        });

        EloquentBuilder::macro('whereLike', static function ($attributes, string $searchTerm) {
            return (new Searchable($searchTerm, Arr::wrap($attributes)))->apply($this);
        });
    }
}

与 Laravel Nova 一起使用

您可以通过在 app/Nova/Resource.php 上添加以下方法来覆盖默认的 Laravel 全局和本地搜索功能。

<?php

namespace App\Nova;

use Laravel\Nova\Http\Requests\NovaRequest;
use Laravel\Nova\Resource as NovaResource;
use Laravie\QueryFilter\Searchable;

abstract class Resource extends NovaResource
{
    // ...
    
    /**
     * Apply the search query to the query.
     *
     * @param \Illuminate\Database\Eloquent\Builder $query
     * @param string                                $search
     *
     * @return \Illuminate\Database\Eloquent\Builder
     */
    protected static function applySearch($query, $search)
    {
        $searchColumns = static::searchableColumns() ?? [];

        return static::initializeSearch($search, $searchColumns)->apply($query);
    }

    /**
     * Initialize Search.
     *
     * @param  string  $search
     * @param  array  $searchColumns
     * @return \Laravie\QueryFilter\Searchable
     */
    protected static function initializeSearch($search, $searchColumns)
    {
        return new Searchable($search, $searchColumns);
    }
}

laravie / query-filter

维护者

详细信息