🔍 Generates database queries based on one unique string

Loris Leiva

Last update: Dec 30, 2022

Related tags

Miscellaneous laravel-search-string

Overview

🔍 Laravel Search String

Generates database queries based on one unique string using a simple and customizable syntax.

Introduction

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', '<=', '2020-12-31 23:59:59')
        ->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.

Installation

# Install via composer
composer require lorisleiva/laravel-search-string

# (Optional) Publish the search-string.php configuration file
php artisan vendor:publish --tag=search-string

Basic usage

Add 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();

The search string syntax

Note that the spaces between operators don't matter.

Exact matches

'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"'

Comparisons

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

Lists

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

Dates

The column must either be cast as a date or explicitly marked as a date in the column options.

// 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

Booleans

The column must either be cast as a boolean or explicitly marked as a boolean in the column options.

Alternatively, if the column is marked as a date, it will automatically be marked as a boolean using is null and is not null.

'published'         // published = true
'created_at'        // created_at is not null

Negations

'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

Null values

The term NULL is case sensitive.

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

Searchable

At 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

And/Or

'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'

Relationships

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.

// 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))

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.

Special keywords

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 10

Configuring columns

Column aliases

If 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)$/',
    // ...
];

Column options

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 [].
    ],
    // ...
];

Key

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.

Date

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', '<=', '2018-07-06 23:59:59');

By default any column that is cast as a date (using Laravel properties), will be marked as a date for LaravelSearchString. You can force a column to not be marked as a date by assigning date to false.

Boolean

If a column is marked as a boolean, it can be used with no operator or value. For example, if the paid column is marked as a boolean:

'paid' // Equivalent to:
$query->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.

Searchable

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.

Relationship

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.

use Lorisleiva\LaravelSearchString\Concerns\SearchString;

class Article extends Model
{
    use SearchString;

    protected $searchStringColumns = [
        'comments' => [
            'key' => '/^comments?$/',   // aliases the column to `comments` or `comment`.
            'relationship' => true,     // There must be a `comments` method that defines a relationship.
        ],
    ];

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

class Comment extends Model
{
    use SearchString;

    protected $searchStringColumns = [
        // ...
    ];
}

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);
}

Configuring special keywords

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',
    ],
    // ...
];

Other places to configure

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 define them directly on the config/search-string.php file like this:

// config/search-string.php
return [
    'default' => [
        'keywords' => [ /* ... */ ],
    ],

    Article::class => [
        'columns'  => [ /* ... */ ],
        'keywords' => [ /* ... */ ],
    ],
];

When resolving the options for a particular model, LaravelSearchString will merge those configurations in the following order:

First using the configurations defined on the model
Then using the config file at the key matching the model class
Then using the config file at the default key
Finally using some fallback configurations

Error handling

The provided search string can be invalid for numerous reasons.

It does not comply to the search string syntax
It tries to query an inexisting column or column alias
It provides invalid values to special keywords like limit
Etc.

Any of those errors will throw an InvalidSearchStringException.

However you can choose whether you want these exceptions to bubble up to the Laravel exception handler or whether you want them to fail silently. For that, you need to choose a fail strategy on your config/search-string.php configuration file:

// config/search-string.php
return [
    'fail' => 'all-results', // (Default) Silently fail with a query containing everything.
    'fail' => 'no-results',  // Silently fail with a query containing nothing.
    'fail' => 'exceptions',  // Throw exceptions.

    // ...
];

Comments

Support case-insensitive search queries
The %% Like searchable is not working. User::usingSearchString('Apple')->toSql(); gives select * from "users"

But, this works

return User::where(function ($query) { $query->where('name', 'like', '%Apple%') ->orWhere('address', 'like', '%Apple%'); })->toSql();

gives select * from "users" where ("name"::text like ? or "address"::text like ?) as expected.

SearchString Trait is added. And, $searchStringColumns is also added properly.

Also, during installation this warning was thrown, It might be related, hoa/ustring suggests installing ext-intl (To get a better Hoa\Ustring::toAscii() and Hoa\Ustring::compareTo().)

Note: Exact matches works just fine.
enhancement
opened by Niush 9
Update Rule.php to get rid of PHP Warning Message

PHP Warning: preg_match(): Delimiter must not be alphanumeric or backslash in .../vendor/lorisleiva/laravel-search-string/src/Options/Rule.php on line 56

Since we are only trying to check if the specified $pattern is wrapped within regex delimiters we could use mb_substr() instead of supressing the output of preg_match() when invalid delimiters are supplied.

opened by mazedlx 7
Supporting relationship queries
Hi there

Thanks for a great package. Hope you don't mind this massive PR out of the blue.

Inspired by #6, I have attempted to support querying for related models. It supports all simple Laravel relationship types (not polymorphic, yet), count queries, and nested relationships. There are also options to configure which relationships are queryable and countable.

I have used a different syntax than your examples in that issue, to make the parser more predictable. I have updated the readme in my branch so you can see all the syntax options possible, but here is how your examples would be expressed:

title: "My article" and has(comments) > 3

title: "My article" and has(comments) (equivalent to has(comments) > 0)

title: "My article" and has(comments { author: johndoe }) >= 1

title: "My article" and has(category { name: Laravel })
or title: "My article" and category.name: Laravel for simplicity

title: "My article" and has(category { name: Laravel or name: Vuejs })

Basically everything inside the { ... } is another expression scoped to the child model, and the has statements can be nested infinitely.

I have written extensive tests, but would really welcome scrutiny on what I've built. I really hope this is useful for you, and will happily take suggestions about how it can be improved.

Cheers!
opened by kitbs 6
Add ability to create enum mapped values in search text

Tried my best to come up with a solution for https://github.com/lorisleiva/laravel-search-string/issues/10.

I went with the simple solution if 2 because the 1 solution seems to be too complicated for me to tackle.

I hope this is suitable for merge.

opened by morloderex 5

Qualified columns

I've qualified columns by adding table name as prefix. Otherwise, queries like the following will fail.

Illuminate\Database\QueryException
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in where clause is ambiguous (SQL: select count(*) as aggregate from `rhj_items` inner join `rhj_categories` on `rhj_categories`.`id` = `rhj_items`.`category_id` where (`name` like %test% or `description` like %test%) and `rhj_items`.`deleted_at` is null and `rhj_items`.`company_id` = 1)

Feel free to edit if I'm missing anything.

opened by denisdulici 4

belongsToMany - how to configure?

I currently have a project where I use a belongsToMany relationship on posts table that links to a tags table. I saw where the documentation says the belongsToMany relationship is supported. The documentation example shows how to use the hasMany relationship. I have added the SearchString trait to both models (Post and Tag) and included the following columns configuration:

'tags' => ['relationship' => true],

Unfortunately I can't seem to get it to work. Should the belongsToMany relationship be configured in a different way?

Ideally I would like users to be able to perform keyword search of the posts table and match it against standard column values or my tag names being displayed as part of a belongsToMany relationship. Is this possible?

Thank you for making a very nice package. All the search options are awesome!

opened by relayer464 4

Error using PHPstan

Using your package seem impossible to use PHPstan, due to the error:

Fatal error: Cannot declare class Hoa\Protocol\Wrapper, because the name is already in use in /Users/luca/Projects/***/vendor/sanmai/hoa-protocol/Source/Wrapper.php on line 44

Check the attachment.

I'm using Laravel 8.60.0 with PHP 8.0.3.

opened by masterix21 3

preg_match does not throw an exception
I don't know why, but in my PHP installation (version 7.4.13) preg_match does not throw an exception if the pattern is not valid. The consequence is that the Rule::regexify method in this package doesn't work as expected. To get it working, I needed to change this:

protected function regexify($pattern) { try { preg_match($pattern, null); return $pattern; } catch (\Throwable $exception) { return '/^' . preg_quote($pattern, '/') . '$/'; } }

to:

protected function regexify($pattern) { try { if (preg_match($pattern, null) === false) { throw new \Exception; } return $pattern; } catch (\Throwable $exception) { return '/^' . preg_quote($pattern, '/') . '$/'; } }

In the first version, I get a bunch of PHP warnings, but no exception, so the catch block is never executed.

I found it strange that I didn't find an issue about this. You can't get correct search results without this change...
opened by klaasgeldof 3

Relationship has searchable columns might be broken

The problem

When searching in a relationship the query builder doesn't add the relationship's searchable columns, it instead searches by the primary model's columns.

Steps to reproduce

App\Models\User.php

namespace App\Models;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;

use Lorisleiva\LaravelSearchString\Concerns\SearchString;

class User extends Authenticatable
{
    use HasFactory, Notifiable, SearchString;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name', 'email', 'password',
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password', 'remember_token',
    ];

    /**
     * The attributes that are searchable.
     */
    protected $searchStringColumns = [
        'name' => ['searchable' => true],
        'email' => ['searchable' => true],
        'posts' => ['key' => '/^posts?$/', 'relationship' => true],
    ];

    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
    ];

    /**
     * Relationships.
     */
    public function posts()
    {
        return $this->hasMany( 'App\Models\Post' );
    }
}

App\Models\Post.php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

use Lorisleiva\LaravelSearchString\Concerns\SearchString;

class Post extends Model
{
    use HasFactory, SearchString;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'user_id', 'title', 'body',
    ];

    /**
     * The attributes that are searchable.
     */
    protected $searchStringColumns = [
        'title' => ['searchable' => true],
    ];

    /**
     * Relationships.
     */
    public function user()
    {
        return $this->belongsTo( 'App\Models\User' );
    }
}

Search string

'posts: (foo)'                   // Has comments whose searchable columns match "%foo%"

Query details

select
  count(*) as aggregate
from
  `users`
where
  exists (
    select
      *
    from
      `posts`
    where
      `users`.`id` = `posts`.`user_id`
      and (
        `name` like '%foo%'
        or `email` like '%foo%'
      )
  )

Expected results

Search string

'posts: (foo)'                   // Has comments whose searchable columns match "%foo%"

Query details

select
  count(*) as aggregate
from
  `users`
where
  exists (
    select
      *
    from
      `posts`
    where
      `users`.`id` = `posts`.`user_id`
      and (
        `title` like '%foo%'
      )
  )

opened by alexjustesen 3

HoaCompiler error after update

First of all, congrats @lorisleiva on the new version, the new grammar and relationships look awesome 😉

I tried to update Akaunting but got the attached error. It's the index page without any search applied yet. Any clue?

Using the latest Laravel on PHP 7.2 version.

opened by denisdulici 3
Searching for an integer enum value by string
In a project I'm working on, I have an Asset model with a support_level_id field, which can be set to the following values:

0: Testing

1: Community

2: Official

I'd like to search for assets with a specific support level by writing something like support_level:official. However, since this package can't search in computed properties, I can only search by ID using support_level_id:2. (In that model, I have a getSupportLevelAttribute() function that returns the support's level name for use in Blade templates.)

Is there a way to implement this somehow? This is quite low-priority, so I could live without it. It'd just make the search string syntax more complete for my use case.

PS: Thanks for creating this library, it's saving me a lot of time :slightly_smiling_face:
enhancement
opened by Calinou 3
`hoa/compiler` is now abandoned

Not so much an issue yet but a heads up.

Recent upgrades alerted me to this and the hoa/compiler which is required dependency of laravel-search-string is now abandoned, but there is no suggested replacement at this time.

opened by slashequip 1
Searching email address

Hi, when search email address "[email protected]" not work, remove @ like "admin" or "email.com" work fine, but can't found a complete email address "[email protected]", how do to search by email address?? Thanks

opened by yashiroiori 2
Database exception raised when sort field doesn't exist

When I set up laravel-search-string, and then use a search string e.g. "sort:invalid-field" then a database exception is raised (Undefined column: 7 ERROR: column table.invalid-field).

I don't think I've done anything wrong (but it's obviously possible).

As search string is exposed to the user, I don't think invalid input should result in an exception, but should fail gracefully. If the native exception can't be caught and handled, then perhaps a the "sortable" columns should be set in the model config?

Note that my fail strategy is set to "all-results".

opened by kurucu 2
Support LIKE operator with specific columns

How can I make the following query return all matches LIKE "My blog article" instead of only exact matches?

Article::usingSearchString('title:"My blog article" or not published sort:-created_at');
enhancement

opened by awinster 2
Custom query scopes

Provides a more flexible API (i.e. column options) that allows us to use a query scope such as scopeMyCustomScope($query, $valueEnteredByTheUser). When such scope is provided, the package will delegate to that scope to build the query.
enhancement

opened by lorisleiva 1

Releases(v1.2.0)

v1.2.0(Feb 24, 2022)
What's Changed

Laravel 9 support by @denisdulici in https://github.com/lorisleiva/laravel-search-string/pull/43

Full Changelog: https://github.com/lorisleiva/laravel-search-string/compare/v1.1.3...v1.2.0
Source code(tar.gz)
Source code(zip)
v1.1.3(Apr 30, 2021)

Make order by and select keyword rules use column aliases (See #34)
Source code(tar.gz)
Source code(zip)
v1.1.2(Mar 21, 2021)
Add case_insensitive option for search queries — defaults to false for backward compatibility. (See #22)

You can set it to true in your options amongst columns and keywords. For example, in the config/search-string.php file:

return [ 'default' => [ 'case_insensitive' => true, // <- Globally. // ... ], Article::class => [ 'case_insensitive' => true, // <- Only for the Article class. // ... ], ];

When set to true, it will lowercase both the column and the value before comparing them using the like operator.

$value = mb_strtolower($value, 'UTF8'); $query->whereRaw("LOWER($column) LIKE ?", ["%$value%"]);
Source code(tar.gz)
Source code(zip)
v1.1.1(Feb 5, 2021)
More robust way to check if a string is a regex (#31)

Do not qualify the column name when already prefixed (#33)

Source code(tar.gz)
Source code(zip)
v1.1.0(Jan 29, 2021)

Use qualify column names to avoid ambiguous conflicts (See #32)
Source code(tar.gz)
Source code(zip)
v1.0.4(Sep 17, 2020)

🚑 Fix searchable columns inside relationships (#27)
Source code(tar.gz)
Source code(zip)
v1.0.3(Sep 13, 2020)

⬆️ Add support for Laravel 8
Source code(tar.gz)
Source code(zip)
v1.0.2(Aug 31, 2020)

Fix resolve conflict with HOA protocol
Source code(tar.gz)
Source code(zip)
v1.0.1(Jul 4, 2020)

🚑 Allow CompilerInterface to handle null input instead of throwing a TypeError (#20).
Source code(tar.gz)
Source code(zip)
v1.0.0(Jul 3, 2020)
What's new?

💍 Relationships are here! (documentation)

You can now query a model's relationships. For example:

comments > 3

comments (equivalent to comments > 0)

category.name: Laravel

category: (name: Laravel or name: Vuejs)

comments: (author: (name: johndoe))

comments.author.name: johndoe

✨ New artisan commands

Three artisan commands have been added allowing you to play with the search string syntax.

php artisan search-string:ast <model> <query>: Dumps the tree (AST) generated by the query.

php artisan search-string:sql <model> <query>: Dumps the SQL generated by the query.

php artisan search-string:get <model> <query>: Returns the results generated by the query.

For example: php artisan search-string:sql User "name = John"

💡 Full re-write of the compiler

The entire parser has been re-written using an Llk compiler compiler (from HOA).

This allows us to define the entire syntax as a grammar that itself gets compiled into a compiler (you can see the grammar here if that's your type of thing). As a result, the generated parser is a lot more robust and more test have been written to make sure of that. Additionally, it makes the parser easier to maintain and to add new features.

Breaking changes

The biggest breaking changes you might come across is the removal of operator and value for the column options. Matching operators was a pointless task since they were hardcoded in the search string syntax and matching the value became redundant when adding the map column option (which I am planning on making more flexible in the future).

Additionally, because the implementation of the parser has changed and became more robust, you might find that some invalid strings that were not failing in the past will now fail as expected.

Source code(tar.gz)
Source code(zip)

v0.1.6(Apr 5, 2020)

✨ Add value mapping. You can now add a map to your column options that will transform any value entered by the user into something that your database understands.

// With these column options...
protected $searchStringColumns = [
    'support_level_id' => [
        'key' => 'support_level',
        'map' => [
            'testing' => 0,
            'community' => 1,
            'official' => 2,
        ]
    ],
];

// "support_level:testing" will generate:
$query->where('support_level_id', '=', 0);

// "support_level:another_value" will generate:
$query->where('support_level_id', '=', 'another_value');

As you can see from that last example, if a user enters a value which is missing from the map option, it will simply be passed as-is. This makes it possible to use the map option as a way to define value aliases.

If you'd like to ensure that any value missing from that mapping generates an InvalidSearchStringException, then you should adjust the value option accordingly. Here is what that would like for our example above.

protected $searchStringColumns = [
    'support_level_id' => [
        'key' => 'support_level',
        'value' => '^testing|community|official$' // <= Whitelist your option using a regex
        'map' => [
            'testing' => 0,
            'community' => 1,
            'official' => 2,
        ]
    ],
];

Source code(tar.gz)
Source code(zip)

v0.1.5(Mar 1, 2020)

⬆️ Add support for Laravel 7
Source code(tar.gz)
Source code(zip)
v0.1.4(Sep 18, 2019)

⬆️ Add support for Laravel 6
Source code(tar.gz)
Source code(zip)
v0.1.3(May 7, 2019)

🚑 Fixes column aliases (#3)
Source code(tar.gz)
Source code(zip)
v0.1.2(Apr 22, 2019)

🚑 Support versions greater than 5.8.0 (#2)
Source code(tar.gz)
Source code(zip)
v0.1.1(Apr 16, 2019)

⬆️ Support Laravel 5.7 and 5.8
Source code(tar.gz)
Source code(zip)
v0.1(May 18, 2018)

Source code(tar.gz)
Source code(zip)