Generates database queries based on one unique string
Generates database queries based on one unique string
Generates database queries based on one unique string using a simple and customizable syntax.
Laravel Search String provides a simple solution for scoping your database queries using a human readable and customizable syntax. It will transform a simple string into a powerful query builder.
For example, the following search string will fetch the latest blog articles that are either not published or titled "My blog article".
Article::usingSearchString('title:"My blog article" or not published sort:-created_at');
// Equivalent to:
Article::where('title', 'My blog article')
->orWhere('published', false)
->orderBy('created_at', 'desc');This next example will search for the term "John" on the customer and description columns whilst making sure the invoices are either paid or archived.
Invoice::usingSearchString('John and status in (Paid,Archived) limit:10 from:10');
// Equivalent to:
Invoice::where(function ($query) {
$query->where('customer', 'like', '%John%')
->orWhere('description', 'like', '%John%');
})
->whereIn('status', ['Paid', 'Archived'])
->limit(10)
->offset(10);You can also query for the existence of related records, for example, articles published in 2020, which have more than 100 comments that are either not spam or written by John.
Article::usingSearchString('published = 2020 and comments: (not spam or author.name = John) > 100');
// Equivalent to:
Article::where('published_at', '>=', '2020-01-01 00:00:00')
->where('published_at', 'whereHas('comments', function ($query) {
$query->where('spam', false)
->orWhereHas('author' function ($query) {
$query->where('name', 'John');
});
}, '>', 100);As you can see, not only it provides a convenient way to communicate with your Laravel API (instead of allowing dozens of query fields), it also can be presented to your users as a tool to explore their data.
# Install via composer
composer require lorisleiva/laravel-search-string
# (Optional) Publish the search-string.php configuration file
php artisan vendor:publish --tag=search-stringAdd the SearchString trait to your models and configure the columns that should be used within your search string.
use Lorisleiva\LaravelSearchString\Concerns\SearchString;
class Article extends Model
{
use SearchString;
protected $searchStringColumns = [
'title', 'body', 'status', 'rating', 'published', 'created_at',
];
}Note that you can define these in other parts of your code and customise the behaviour of each column.
That's it! Now you can create a database query using the search string syntax.
Article::usingSearchString('title:"Hello world" sort:-created_at,published')->get();Note that the spaces between operators don't matter.
'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"''title 3'
'created_at >= "2018-07-06 00:00:00"''title in (Hello, Hi, "My super article")'
'status in(Finished,Archived)'
'status:Finished,Archived'The column must either be cast as a date or explicitly marked as a date in the column options.
…The term NULL is case sensitive.
'body:NULL' // body is null
'not body:NULL' // body is not nullAt least one column must be defined as searchable.
The queried term must not match a boolean column, otherwise it will be handled as a boolean query.
'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'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'The column must be explicitly defined as a relationship and the model associated with this relationship must also use the SearchString trait.
When making a nested query within a relationship, Laravel Search String will use the column definition of the related model.
In the following examples, comments is a HasMany relationship and author is a nested BelongsTo relationship within the Comment model.
…Note that all these expressions delegate to the has query method. Therefore, it works out-of-the-box with the following relationship types: HasOne, HasMany, HasOneThrough, HasManyThrough, BelongsTo, BelongsToMany, MorphOne, MorphMany and MorphToMany.
The only relationship type currently not supported is MorphTo since Laravel Search String needs an explicit related model to use withing nested queries.
Note that these keywords can be customised.
'fields:title,body,created_at' // Select only title, body, created_at
'not fields:rating' // Select all columns but rating
'sort:rating,-created_at' // Order by rating asc, created_at desc
'limit:1' // Limit 1
'from:10' // Offset 10If you want a column to be queried using a different name, you can define it as a key/value pair where the key is the database column name and the value is the alias you wish to use.
protected $searchStringColumns = [
'title',
'body' => 'content',
'published_at' => 'published',
'created_at' => 'created',
];You can also provide a regex pattern for a more flexible alias definition.
protected $searchStringColumns = [
'published_at' => '/^(published|live)$/',
// ...
];You can configure a column even further by assigning it an array of options.
protected $searchStringColumns = [
'created_at' => [
'key' => 'created', // Default to column name: /^created_at$/
'date' => true, // Default to true only if the column is cast as date.
'boolean' => true, // Default to true only if the column is cast as boolean or date.
'searchable' => false // Default to false.
'relationship' => false // Default to false.
'map' => ['x' => 'y'] // Maps data from the user input to the database values. Default to [].
],
// ...
];The key option is what we've been configuring so far, i.e. the alias of the column. It can be either a regex pattern (therefore allowing multiple matches) or a regular string for an exact match.
If a column is marked as a date, the value of the query will be parsed using Carbon whilst keeping the level of precision given by the user. For example, if the created_at column is marked as a date:
'created_at >= tomorrow' // Equivalent to:
$query->where('created_at', '>=', 'YYYY-MM-DD 00:00:00');
// where `YYYY-MM-DD` matches the date of tomorrow.
'created_at = "July 6, 2018"' // Equivalent to:
$query->where('created_at', '>=', '2018-07-06 00:00:00');
->where('created_at', 'where('paid', true);
'not paid' // Equivalent to:
$query->where('paid', false);If a column is marked as both boolean and date, it will be compared to null when used as a boolean. For example, if the published_at column is marked as boolean and date and uses the published alias:
'published' // Equivalent to:
$query->whereNotNull('published');
'not published_at' // Equivalent to:
$query->whereNull('published');By default any column that is cast as a boolean or as a date (using Laravel properties), will be marked as a boolean. You can force a column to not be marked as a boolean by assigning boolean to false.
If a column is marked as searchable, it will be used to match search queries, i.e. terms that are alone but are not booleans like Apple Banana or "John Doe".
For example if both columns title and description are marked as searchable:
'Apple Banana' // Equivalent to:
$query->where(function($query) {
$query->where('title', 'like', '%Apple%')
->orWhere('description', 'like', '%Apple%');
})
->where(function($query) {
$query->where('title', 'like', '%Banana%')
->orWhere('description', 'like', '%Banana%');
});
'"John Doe"' // Equivalent to:
$query->where(function($query) {
$query->where('title', 'like', '%John Doe%')
->orWhere('description', 'like', '%John Doe%');
});If no searchable columns are provided, such terms or strings will be ignored.
If a column is marked as a relationship, it will be used to query relationships.
The column name must match a valid relationship method on the model but, as usual, aliases can be created using the key option.
The model associated with that relationship method must also use the SearchString trait in order to nest relationship queries.
For example, say you have an Article Model and you want to query its related comments. Then, there must be a valid comments relationship method and the Comment model must itself use the SearchString trait.
…Note that, since Laravel Search String is simply delegating to the $builder->has(...) method, you can provide any fancy relationship method you want and the constraints will be kept. For example:
protected $searchStringColumns = [
'myComments' => [
'key' => 'my_comments',
'relationship' => true,
],
];
public function myComments()
{
return $this->hasMany(Comment::class)->where('author_id', Auth::user()->id);
}You can customise the name of a keyword by defining a key/value pair within the $searchStringKeywords property.
protected $searchStringKeywords = [
'select' => 'fields', // Updates the selected query columns
'order_by' => 'sort', // Updates the order of the query results
'limit' => 'limit', // Limits the number of results
'offset' => 'from', // Starts the results at a further index
];Similarly to column values you can provide an array to define a custom key of the keyword. Note that the date, boolean, searchable and relationship options are not applicable for keywords.
protected $searchStringKeywords = [
'select' => [
'key' => 'fields',
],
// ...
];As we've seen so far, you can configure your columns and special keywords using the searchStringColumns and searchStringKeywords properties on your model.
You can also override the getSearchStringOptions method on your model which defaults to:
public function getSearchStringOptions()
{
return [
'columns' => $this->searchStringColumns ?? [],
'keywords' => $this->searchStringKeywords ?? [],
];
}If you'd rather not define any of these configurations on the model itself, you can d
No open issues yet, or sync has not completed.