Pattern-matching search for Laravel eloquent models.
- Currently supports MySQL only.
- Helpful for complex table queries with multiple joins and derived columns.
- Fluent columns definitions.
See demo project.
Simple setup for searchable model and can search on derived columns.
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();Imagine we have an api for a table or list that has searching and column sorting and pagination. This is a usual setup for a table or list. The internal explanations will be available on the documentation below. Our api call may look like this:
http://awesome-app.com/api/posts?per_page=10&page=1&sort_by=title&descending=true&search=SomePostTitle
Your code can look like this:
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();If you want to sort by relevance, call method sortByRelevance() after search() method.
This will addSelect field sort_index which will be used to order or sort by relevance.
Example:
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']
]);You can define columns to be only sortable but not be part of search query constraint.
Just put it under sortable_columns as shown below .
This column can be easily access to put in orderBy of query builder. All searchable columns are also sortable columns.
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.Override the deafultSearchQuery in the model like so:
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}%'"
});
}
}You may also check the build query by dd-ing it:
$query = User::search('John Doe');
dd($query->toSql());which may output to
select * from users where `column` like 'John Doe'
// or
select * from users where `column` like '%John Doe%'
Usually we have queries that has a derived columns like our example for Post's author_full_name.
Sometimes we need to sort our query results by this column.
$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();which may output to
-- (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;- Get the table columns.
TableColumns::get('posts');- Identifies if the column is a valid column, either a regular table column or derived column.
- Useful for checking valid columns to avoid sql injection especially in
orderByquery, see post.
$query->getModel()->isColumnValid(request('sort_by'));- Enable the searchable behavior.
$query->getModel()->enableSearchable();
$query->search('foo');- Disable the searchable behavior.
- Calling
search()method will not perform a search.
$query->getModel()->disableSearchable();
$query->search('foo');- Set or override the model's
$searchableproperty. - Useful for building searchable config on runtime.
$query->getModel()->setSearchable([
'columns' => ['title', 'status'],
'joins' => [...],
]);
$query->search('foo');- Add columns or joins in the model's
$searchableproperty. - Useful for building searchable config on runtime.
$query->getModel()->addSearchable([
'columns' => ['title', 'status'],
'joins' => [...],
]);
$query->search('foo');Calling select() after search() will overwrite sort_index field, so it is recommended to call select()
before search(). Or you can use addSelect() instead.
- Ray Anthony Madrona @raymadrona, for the tips on using MySQL
LOCATE()for sort relevance. - nicolaslopezj/searchable, for the
$searchableproperty declaration style.