README

根据搜索语法生成数据库查询。

英文 | 中文

🏗 场景

中后台系统
复杂的前端查询条件

<?php

namespace App\Http\Controllers;

use App\Models\Post;
use Illuminate\Http\Request;

class PostsController extends Controller
{
    public function index(Request $request)
    {
        return Post::search($request->all())->get();
    }
}

✨ 功能

零配置
兼容laravel/scout和lorisleiva/laravel-search-string
支持字符串和数组语法
支持laravel框架
支持thinkphp框架

📦 安装

通过composer安装

composer require illusionist/searcher

🔨 使用

将Searchable特质添加到你的模型中

Laravel/Lumen

<?php

namnespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illusionist\Searcher\Contracts\Searchable as SearchableContract;
use Illusionist\Searcher\Eloquent\Searchable;

class Post extends Model implements SearchableContract
{
    use Searchable;
}

ThinkPHP

您的ThinkPHP版本必须是>= 5.x

<?php

namnespace app\model;

use think\Model;
use Illusionist\Searcher\Contracts\Searchable as SearchableContract;
use Illusionist\Searcher\Eloquent\Searchable;

class Post extends Model implements SearchableContract
{
    use Searchable;
}

现在您可以使用搜索语法创建数据库查询

Post::search('title:"Hello world" sort:-created_at,published')->get();

💡 语法

注意，对于字符串语法，操作符之间的空格无关紧要

精确匹配

字符串语法

'rating: 0'
'rating = 0'
'title: Hello'               // Strings without spaces do not need quotes
'title: "Hello World"'       // Strings with spaces require quotes
"title: 'Hello World'"       // Single quotes can be used too
'rating = 99.99'
'created_at: "2018-07-06 00:00:00"'

数组语法

['rating' => 0]
['title' => 'Hello World']
['rating' => 99.99]
['created_at' => '2018-07-06 00:00:00']

比较

字符串语法

'title < B'
'rating > 3'
'created_at >= "2018-07-06 00:00:00"'

数组语法

['title' => ['<', 'B']]
['rating' => ['>', 3]]
['created_at' => ['>=', '2018-07-06 00:00:00']]

布尔值

字符串语法

'published'         // published = true
'not published'     // published = false

数组语法

['published']              // published = true
['not' => 'published']    // published = false

日期

字符串语法

'created_at'                            // created_at is not null
'not created_at'                        // created_at is null

// Year precision
'created_at >= 2020'                    // 2020-01-01 00:00:00 <= created_at
'created_at > 2020'                     // 2020-12-31 23:59:59 < created_at
'created_at = 2020'                     // 2020-01-01 00:00:00 <= created_at <= 2020-12-31 23:59:59
'not created_at = 2020'                 // created_at < 2020-01-01 00:00:00 and created_at > 2020-12-31 23:59:59

// Month precision
'created_at = 01/2020'                  // 2020-01-01 00:00:00 <= created_at <= 2020-01-31 23:59:59
'created_at <= "Jan 2020"'              // created_at <= 2020-01-31 23:59:59
'created_at < 2020-1'                   // created_at < 2020-01-01 00:00:00

// Day precision
'created_at = 2020-12-31'               // 2020-12-31 00:00:00 <= created_at <= 2020-12-31 23:59:59
'created_at >= 12/31/2020"'             // 2020-12-31 23:59:59 <= created_at
'created_at > "Dec 31 2020"'            // 2020-12-31 23:59:59 < created_at

// Hour and minute precisions
'created_at = "2020-12-31 16"'          // 2020-12-31 16:00:00 <= created_at <= 2020-12-31 16:59:59
'created_at = "2020-12-31 16:30"'       // 2020-12-31 16:30:00 <= created_at <= 2020-12-31 16:30:59
'created_at = "Dec 31 2020 5pm"'        // 2020-12-31 17:00:00 <= created_at <= 2020-12-31 17:59:59
'created_at = "Dec 31 2020 5:15pm"'     // 2020-12-31 17:15:00 <= created_at <= 2020-12-31 17:15:59

// Exact precision
'created_at = "2020-12-31 16:30:00"'    // created_at = 2020-12-31 16:30:00
'created_at = "Dec 31 2020 5:15:10pm"'  // created_at = 2020-12-31 17:15:10

// Relative dates
'created_at = today'                    // today between 00:00 and 23:59
'not created_at = today'                // any time before today 00:00 and after today 23:59
'created_at >= tomorrow'                // from tomorrow at 00:00
'created_at <= tomorrow'                // until tomorrow at 23:59
'created_at > tomorrow'                 // from the day after tomorrow at 00:00
'created_at < tomorrow'                 // until today at 23:59

数组语法

['created_at']                                      // created_at is not null
['not' => 'created_at']                             // created_at is null

// Year precision
['created_at' => ['>=', '2020']]                    // 2020-01-01 00:00:00 <= created_at
['created_at' => ['>', '2020']]                     // 2020-12-31 23:59:59 < created_at
['created_at' => '2020']                            // 2020-01-01 00:00:00 <= created_at <= 2020-12-31 23:59:59
['not' => ['created_at' => '2020']]                 // created_at < 2020-01-01 00:00:00 and created_at > 2020-12-31 23:59:59

// Month precision
['created_at' => '01/2020']                         // 2020-01-01 00:00:00 <= created_at <= 2020-01-31 23:59:59
['created_at' => ['<=', 'Jan 2020']                 // created_at <= 2020-01-31 23:59:59
['created_at' => ['<', '2020-1']]                   // created_at < 2020-01-01 00:00:00

// Day precision
['created_at' => '2020-12-31']                      // 2020-12-31 00:00:00 <= created_at <= 2020-12-31 23:59:59
['created_at' => ['>=', '12/31/2020']               // 2020-12-31 23:59:59 <= created_at
['created_at' => ['>', 'Dec 31 2020']]              // 2020-12-31 23:59:59 < created_at

// Hour and minute precisions
['created_at' => '2020-12-31 16']                   // 2020-12-31 16:00:00 <= created_at <= 2020-12-31 16:59:59
['created_at' => '2020-12-31 16:30']                // 2020-12-31 16:30:00 <= created_at <= 2020-12-31 16:30:59
['created_at' => 'Dec 31 2020 5pm']                 // 2020-12-31 17:00:00 <= created_at <= 2020-12-31 17:59:59
['created_at' => 'Dec 31 2020 5:15pm']              // 2020-12-31 17:15:00 <= created_at <= 2020-12-31 17:15:59

// Exact precision
['created_at' => '2020-12-31 16:30:00']             // created_at = 2020-12-31 16:30:00
['created_at' => 'Dec 31 2020 5:15:10pm']           // created_at = 2020-12-31 17:15:10

// Relative dates
['created_at' => 'today']                           // today between 00:00 and 23:59
['not' => ['created_at' => 'today']]                // any time before today 00:00 and after today 23:59
['created_at' => ['>=', 'tomorrow']]                // from tomorrow at 00:00
['created_at' => ['<=', 'tomorrow']]                // until tomorrow at 23:59
['created_at' => ['>', 'tomorrow']]                 // from the day after tomorrow at 00:00
['created_at' => ['<', 'tomorrow']]                 // until today at 23:59

列表

字符串语法

'status:Finished,Archived'
'status in(Finished,Archived)'
'title in (Hello, Hi, "My super article")'

数组语法

['status' => ['Finished', 'Archived']]
['status' => ['in', 'Finished', 'Archived']]
['title' => ['in', 'Hello', 'Hi', 'My super article']]

介于

字符串语法

'created_at:2021-1-1~2021-12-31'
'created_at between(2021-1-1, 2021-12-31)'

数组语法

['created_at' => ['between', ['2021-1-1', '2021-12-31']]]
['created_at' => ['between', '2021-1-1', '2021-12-31']]

否定

字符串语法

'not title:Hello'
'not title="My super article"'
'not rating:0'
'not rating>4'
'not status in (Finished,Archived)'
'not published'                         // published = false
'not created_at'                        // created_at is null

数组语法

['not' => ['title' => 'Hello']]
['not' => ['rating' => 0]]
['not' => ['rating' => ['>', 4]]]
['not' => ['status' => ['in', 'Finished', 'Archived']]]
['not' => ['published']]                                   // published = false
['not' => ['created_at']]                                  // created_at is null

空值

字符串语法

术语NULL不区分大小写。

'body:NULL'         // body is null
'not body:null'     // body is not null

数组语法

['body' => null]               // body is null
['not' => ['body' => null]]    // body is not null

可搜索

查询的术语不能与布尔值或日期列匹配，否则它将被处理为布尔值或日期查询。

字符串语法

'Apple'             // %Apple% like at least one of the searchable columns
'"John Doe"'        // %John Doe% like at least one of the searchable columns
'not "John Doe"'    // %John Doe% not like any of the searchable columns

数组语法

['Apple']                  // %Apple% like at least one of the searchable columns
['not' => 'John Doe']      // %John Doe% not like any of the searchable columns

与/或

字符串语法

'title:Hello body:World'        // Implicit and
'title:Hello and body:World'    // Explicit and
'title:Hello or body:World'     // Explicit or
'A B or C D'                    // Equivalent to '(A and B) or (C and D)'
'A or B and C or D'             // Equivalent to 'A or (B and C) or D'
'(A or B) and (C or D)'         // Explicit nested priority
'not (A and B)'                 // Equivalent to 'not A or not B'
'not (A or B)'                  // Equivalent to 'not A and not B'

数组语法

关键字使用studly-caps格式，例如andOr可以写成and_or或and-or或and or或AndOr;

['title' => 'Hello', 'body' => 'World']                // Implicit and
['and' => ['title' => 'Hello', 'body' => 'World']]     // Explicit and
['or' => ['title' => 'Hello', 'body' => 'World']]      // Explicit or
['or' => [['A', 'B'], ['C', 'D']]]                     // Equivalent to '(A and B) or (C and D)'
['or' => ['A', ['B', 'C'], 'D']]                       // Equivalent to 'A or (B and C) or D'
['andOr' => [['A', 'B'], ['C', 'D']]]                  // Equivalent to '(A or B) and (C or D)'
['not' => ['A', 'B']]                                  // Equivalent to 'not A or not B'
['notOr' => ['A', 'B']]                                // Equivalent to 'not A and not B'

关系

字符串语法

// Simple "has" check
'comments'                              // Has comments
'not comments'                          // Doesn't have comments
'comments = 3'                          // Has 3 comments
'not comments = 3'                      // Doesn't have 3 comments
'comments > 10'                         // Has more than 10 comments
'not comments <= 10'                    // Same as before
'comments <= 5'                         // Has 5 or less comments
'not comments > 5'                      // Same as before

// "WhereHas" check
'comments: (title: Superbe)'            // Has comments with the title "Superbe"
'comments: (not title: Superbe)'        // Has comments whose titles are different than "Superbe"
'not comments: (title: Superbe)'        // Doesn't have comments with the title "Superbe"
'comments: (quality)'                   // Has comments whose searchable columns match "%quality%"
'not comments: (spam)'                  // Doesn't have comments marked as spam
'comments: (spam) >= 3'                 // Has at least 3 spam comments
'not comments: (spam) >= 3'             // Has at most 2 spam comments
'comments: (not spam) >= 3'             // Has at least 3 comments that are not spam
'comments: (likes < 5)'                 // Has comments with less than 5 likes
'comments: (likes < 5) <= 10'           // Has at most 10 comments with less than 5 likes
'not comments: (likes < 5)'             // Doesn't have comments with less than 5 likes
'comments: (likes > 10 and not spam)'   // Has non-spam comments with more than 10 likes

// "WhereHas" shortcuts
'comments.title: Superbe'               // Same as 'comments: (title: Superbe)'
'not comments.title: Superbe'           // Same as 'not comments: (title: Superbe)'
'comments.spam'                         // Same as 'comments: (spam)'
'not comments.spam'                     // Same as 'not comments: (spam)'
'comments.likes < 5'                    // Same as 'comments: (likes < 5)'
'not comments.likes < 5'                // Same as 'not comments: (likes < 5)'

// Nested relationships
'comments: (author: (name: John))'      // Has comments from the author named John
'comments.author: (name: John)'         // Same as before
'comments.author.name: John'            // Same as before

// Nested relationships are optimised
'comments.author.name: John and comments.author.age > 21'   // Same as: 'comments: (author: (name: John and age > 21))
'comments.likes > 10 or comments.author.age > 21'           // Same as: 'comments: (likes > 10 or author: (age > 21))

数组语法

// Simple "has" check
['comments']                                               // Has comments
['not' => ['comments']]                                    // Doesn't have comments
['comments' => 3]                                          // Has 3 comments
['not' => ['comments' => 3]]                               // Doesn't have 3 comments
['comments' => ['>', 10]]                                  // Has more than 10 comments
['not' => ['comments' => ['<=', 10]]]                      // Same as before
['comments' => ['<=', 5]]                                  // Has 5 or less comments
['not' => ['comments' => ['>', 5]]]                        // Same as before

// "WhereHas" check
['comments' => ['title' => 'Superbe']]                     // Has comments with the title "Superbe"
['comments' => ['not' => ['title' => 'Superbe']]]          // Has comments whose titles are different than "Superbe"        
['not' => ['comments' => ['title' => 'Superbe']]]          // Doesn't have comments with the title "Superbe"
['comments' => 'quality']                                  // Has comments whose searchable columns match "%quality%"
['not' => ['comments' => 'spam']]                          // Doesn't have comments marked as spam
['comments' => ['spam', ['>=', 3]]]                        // Has at least 3 spam comments
['not' => ['comments' => ['spam', ['>=', 3]]]]             // Has at most 2 spam comments
['comments' => ['not' => 'spam', ['>=', 3]]]               // Has at least 3 comments that are not spam
['comments' => ['likes' => ['<', 5]]]                      // Has comments with less than 5 likes
['comments' => ['likes' => ['<', 5], ['<=', 10]]]          // Has at most 10 comments with less than 5 likes
['not' => ['comments' => ['likes' => ['<', 5]]]]           // Doesn't have comments with less than 5 likes
['comments' => ['likes' => ['<', 5], 'not' => 'spam']]     // Has non-spam comments with more than 10 likes

// Nested relationships
['comments' => ['author' => ['name' => 'John']]]           // Has comments from the author named John

⚔️ 高级

可搜索

如果一个查询术语不是布尔值或日期列，它将调用getQueryPhraseColumns以获取可搜索的列。

如果返回值中没有指定操作符，则默认使用like。

例如

<?php

namnespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illusionist\Searcher\Contracts\Searchable as SearchableContract;
use Illusionist\Searcher\Eloquent\Searchable;

class Post extends Model implements SearchableContract
{
    use Searchable;

    /**
     * Get the columns of the query phrase.
     *
     * @param  string  $phrase
     * @return array
     */
    public function getQueryPhraseColumns($phrase)
    {
        if (is_numeric($phrase)) {
            return ['stars' => '>=', 'comments.stars' => '>='];
        }

        return ['title'];
    }
}

'lonely' // Equivalent to:
$query->where('title', '%lonely%');

'3000' // Equivalent to:
$query->where(function ($query) {
    $query->where('stars', '>=', '3000', 'or')
        ->whereHas('comments', function ($query) {
            $query->where('stars', '>=', '3000')
        });
});

关系

如果您定义了一个关系方法，它将用于查询关系。

<?php

namnespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illusionist\Searcher\Contracts\Searchable as SearchableContract;
use Illusionist\Searcher\Eloquent\Searchable;

class Post extends Model implements SearchableContract
{
    use Searchable;

    public function comments()
    {
        return $this->hasMany(Comment::class);
    }
}

// Querying Relationship Existence
'comments'                          // $query->has('comments');

// Counting Related Models
'select:comments_count'            // $query->withCount('comments');

// Eager Loading
'select:comments'                  // $query->select('id')->with('comments');
'select:comments.title'            // $query->select('id')->with('comments:id,title')

配置可搜索列

不在searchable属性中的查询术语将被丢弃，默认值是模型表的实际列和关系方法名称。

<?php

namnespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illusionist\Searcher\Contracts\Searchable as SearchableContract;
use Illusionist\Searcher\Eloquent\Searchable;

class Post extends Model implements SearchableContract
{
    use Searchable;

    protected $searchable = ['author', 'created_at'];
}

'author:kayson title:hello'  // Equivalent to:
$query->where('author', '=', 'kayson');

配置布尔值和日期列

Laravel/Lumen

使用casts属性指定布尔值和日期列。

<?php

namnespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illusionist\Searcher\Contracts\Searchable as SearchableContract;
use Illusionist\Searcher\Eloquent\Searchable;

class Post extends Model implements SearchableContract
{
    use Searchable;

    protected $casts = [
        'published' => 'boolean',
        'created_at' => 'datetime',
    ];
}

ThinkPHP

使用type属性指定布尔值和日期列。

<?php

namnespace app\model;

use think\Model;
use Illusionist\Searcher\Contracts\Searchable as SearchableContract;
use Illusionist\Searcher\Eloquent\Searchable;

class Post extends Model implements SearchableContract
{
    use Searchable;

    protected $type = [
        'published' => 'boolean',
        'created_at' => 'datetime',
    ];
}

配置特殊关键字

通过重写getRelaSearchName函数实现自定义关键字和共生列。

selec、order_by、offset是保留关键字，请勿与查询术语冲突。

<?php

namnespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illusionist\Searcher\Contracts\Searchable as SearchableContract;
use Illusionist\Searcher\Eloquent\Searchable;

class Post extends Model implements SearchableContract
{
    use Searchable;

    /**
     * Get the real name of the given search column.
     *
     * @param  string  $key
     * @return string|array
     */
    public function getRelaSearchName($key)
    {
       switch ($key) {
            case 'field':
                return 'select';
            case 'sort':
                return 'order_by';
            case 'from':
                return 'offset';
            case 'stars':
                return ['stars', 'comments.stars'];
            default:
                return $key;
        }
    }
}

'field:id,name' // Equivalent to:
$query->select(['id', 'name']);

'stars:3000' // Equivalent to:
$query->where(function ($query) {
    $query->where('stars', '>=', '3000', 'or')
        ->whereHas('comments', function ($query) {
            $query->where('stars', '>=', '3000')
        });
});

幻术师 / 搜索器

维护者

详细信息

README

🏗 场景

✨ 功能

📦 安装

🔨 使用

Laravel/Lumen

ThinkPHP

💡 语法

精确匹配

比较

布尔值

日期

列表

介于

否定

空值

可搜索

与/或

关系

⚔️ 高级

可搜索

关系

配置可搜索列

配置布尔值和日期列

Laravel/Lumen

ThinkPHP

配置特殊关键字