README

用于Laravel eloquent模型的模式匹配搜索。

目前仅支持MySQL。
适用于复杂的多表连接和派生列的查询。
流畅的列定义。

演示项目

概述

简单的可搜索模型设置，并且可以搜索派生列。

use AjCastro\Searchable\Searchable;

class Post
{
    use Searchable;

    protected $searchable = [
        // This will search on the defined searchable columns
        'columns' => [
            'posts.title',
            'posts.body',
            'author_full_name' => 'CONCAT(authors.first_name, " ", authors.last_name)'
        ],
        'joins' => [
            'authors' => ['authors.id', 'posts.author_id']
        ]
    ];

    public function author()
    {
        return $this->belongsTo(Author::class);
    }
}

// Usage
Post::search("Some title or body content or even the author's full name")
    ->with('author')
    ->paginate();

想象我们有一个API，用于具有搜索、列排序和分页的表或列表。这是表或列表的常见设置。内部解释将在以下文档中提供。我们的API调用可能看起来像这样

http://awesome-app.com/api/posts?per_page=10&page=1&sort_by=title&descending=true&search=SomePostTitle

您的代码可以像这样

class PostsController
{
    public function index(Request $request)
    {
        $query = Post::query();

        return $query
            ->with('author')
            // advance usage with custom search string parsing
            ->when($request->parse_using === 'exact', function ($query) {
                $query->parseUsing(function ($searchStr) {
                    return "%{$searchStr}%";
                });
            })
            ->search($request->search)
            ->when(
                $request->has('sort_by') && $query->getModel()->isColumnValid($request->sort_by),
                function ($query) use ($request) {
                    $query->orderBy(
                        DB::raw($query->getModel()->getColumn($request->sort_by)),
                        $request->descending ? 'desc' : 'asc'
                    );
                },
                function ($query) {
                    $query->sortByRelevance();
                },
            )
            ->paginate();
    }

}

文档

安装

composer require ajcastro/searchable

可搜索模型

use AjCastro\Searchable\Searchable;

class Post extends Model
{
    use Searchable;

    /**
     * Searchable model definitions.
     */
     protected $searchable = [
        // Searchable columns of the model.
        // If this is not defined it will default to all table columns.
        'columns' => [
            'posts.title',
            'posts.body',
            'author_full_name' => 'CONCAT(authors.first_name, " ", authors.last_name)'
        ],
        // This is needed if there is a need to join other tables for derived columns.
        'joins' => [
            'authors' => ['authors.id', 'posts.author_id'], // defaults to leftJoin method of eloquent builder
            'another_table' => ['another_table.id', 'authors.another_table_id', 'join'], // can pass leftJoin, rightJoin, join of eloquent builder.
        ]
    ];

    /**
     * Can also be written like this for searchable columns.
     *
     * @var array
     */
    protected $searchableColumns = [
        'title',
        'body',
        'author_full_name' => 'CONCAT(authors.first_name, " ", authors.last_name)'
    ];

    /**
     * Can also be written like this for searchable joins.
     *
     * @var array
     */
    protected $searchableJoins = [
        'authors' => ['authors.id', 'posts.author_id']
    ];
}

// Usage
// Call search anywhere
// This only search on the defined columns.
Post::search('Some post')->paginate();
Post::where('likes', '>', 100)->search('Some post')->paginate();

如果您想按相关性排序，请在调用search()方法后调用sortByRelevance()方法。这将添加sort_index选择字段，该字段将用于按相关性排序或排序。

示例

Post::search('Some post')->sortByRelevance()->paginate();
Post::where('likes', '>', 100)->search('Some post')->sortByRelevance()->paginate();

在运行时设置可搜索配置。

$post = new Post;
$post->setSearchable([ // addSearchable() method is also available
    'columns' => [
        'posts.title',
        'posts.body',
    ],
    'joins' => [
        'authors' => ['authors.id', 'posts.author_id']
    ]
]);
// or
$post->setSearchableColumns([ // addSearchableColumns() method is also available
    'posts.title',
    'posts.body',
]);
$post->setSearchableJoins([ // addSearchableJoins() method is also available
    'authors' => ['authors.id', 'posts.author_id']
]);

易于排序的列

您可以将列定义为仅可排序但不作为搜索查询约束的一部分。只需将其放在以下sortable_columns下，如所示。此列可以轻松访问以放入查询构建器的orderBy中。所有可搜索列也都是可排序列。

class Post {
     protected $searchable = [
        'columns' => [
            'title' => 'posts.title',
        ],
        'sortable_columns' => [
            'status_name' => 'statuses.name',
        ],
        'joins' => [
            'statuses' => ['statuses.id', 'posts.status_id']
        ]
    ];
}

// Usage

Post::search('A post title')->orderBy(Post::make()->getSortableColumn('status_name'));
// This will only perform search on `posts`.`title` column and it will append "order by `statuses`.`name`" in the query.
// This is beneficial if your column is mapped to a different column name coming from front-end request.

自定义搜索字符串解析器 - 精确搜索示例

在模型中覆盖defaultSearchQuery，如下所示

use AjCastro\Searchable\BaseSearch;

class User extends Model
{
    public function defaultSearchQuery()
    {
        return BaseSearch::make($this->buildSearchableColumns())
            ->parseUsing(function ($searchStr) {
                return $searchStr; // produces "where `column` like '{$searchStr}'"
                return "%{$searchStr}%"; // produces "where `column` like '%{$searchStr}%'"
            });
    }
}

您还可以通过dd-ing它来检查构建的查询

$query = User::search('John Doe');
dd($query->toSql());

可能输出为

select * from users where `column` like 'John Doe'
// or
select * from users where `column` like '%John Doe%'

使用派生列进行排序和WHERE条件

通常我们有一些具有派生列的查询，如我们的示例中的Post的author_full_name。有时我们需要按此列对查询结果进行排序。

$query = Post::query();
$post = $query->getModel();
// (A)
$query->search('Some search')->orderBy($post->getColumn('author_full_name'), 'desc')->paginate();
// (B)
$query->search('Some search')->where($post->getColumn('author_full_name'), 'William%')->paginate();

可能输出为

-- (A)
select * from posts where ... order by CONCAT(authors.first_name, " ", authors.last_name) desc limit 0, 15;
-- (B)
select * from posts where ... and CONCAT(authors.first_name, " ", authors.last_name) like 'William%' limit 0, 15;

可用辅助方法

TableColumns::get() [静态]

获取表列。

TableColumns::get('posts');

isColumnValid

识别列是否为有效列，无论是常规表列还是派生列。
用于检查有效列以避免SQL注入，特别是在orderBy查询中，查看文章。

$query->getModel()->isColumnValid(request('sort_by'));

enableSearchable

启用可搜索行为。

$query->getModel()->enableSearchable();
$query->search('foo');

disableSearchable

禁用可搜索行为。
调用search()方法不会执行搜索。

$query->getModel()->disableSearchable();
$query->search('foo');

setSearchable

设置或覆盖模型的$searchable属性。
在运行时构建可搜索配置时很有用。

$query->getModel()->setSearchable([
  'columns' => ['title', 'status'],
  'joins' => [...],
]);
$query->search('foo');

addSearchable

向模型的$searchable属性中添加列或连接。
在运行时构建可搜索配置时很有用。

$query->getModel()->addSearchable([
  'columns' => ['title', 'status'],
  'joins' => [...],
]);
$query->search('foo');

警告

在调用select()之后调用search()将覆盖sort_index字段，因此建议在调用search()之前调用select()。或者您可以使用addSelect()代替。

致谢

Ray Anthony Madrona @raymadrona，对于使用MySQL LOCATE()进行排序相关性的提示。
nicolaslopezj/searchable，对于$searchable属性声明风格。

ajcastro / searchable

维护者

详细信息