Searchable Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require ajcastro/searchable
   

   Publish the config (if needed):

   php artisan vendor:publish --provider="AjCastro\Searchable\SearchableServiceProvider"
   

2. Define Searchable Model: Add the Searchable trait to your Eloquent model and configure $searchable:

   use AjCastro\Searchable\Searchable;
   
   class Post extends Model
   {
       use Searchable;
   
       protected $searchable = [
           'columns' => [
               'posts.title',
               'posts.body',
               'author_full_name' => 'CONCAT(authors.first_name, " ", authors.last_name)'
           ],
           'joins' => [
               'authors' => ['authors.id', 'posts.author_id']
           ]
       ];
   }
   

3. First Search Query:

   $results = Post::search('query')->paginate(10);
   

First Use Case: API Endpoint

For a searchable API endpoint (e.g., /api/posts):

use AjCastro\Searchable\Searchable;
use Illuminate\Http\Request;

class PostController extends Controller
{
    public function index(Request $request)
    {
        $search = $request->input('search', '');
        $perPage = $request->input('per_page', 10);

        $query = Post::search($search)->paginate($perPage);

        return response()->json($query);
    }
}

Implementation Patterns

Core Workflow: Search Integration

1. Define Searchable Columns:

   • Use raw SQL expressions for derived columns (e.g., CONCAT()).
   • Reference joined tables via dot notation (e.g., authors.first_name).

   'columns' => [
       'posts.title',
       'posts.body',
       'custom_field' => 'UPPER(posts.title) AS custom_field'
   ]
   

2. Handle Joins:

   • Declare joins in $searchable['joins'] to ensure columns are available.
   • Example: Search across posts and authors tables.

   'joins' => [
       'authors' => ['authors.id', 'posts.author_id']
   ]
   

3. API Request Handling:

   • Extract search term from request (e.g., $request->search).
   • Chain with pagination/sorting:

   $query = Post::search($request->search)
               ->orderBy($request->sort_by ?? 'created_at')
               ->paginate($request->per_page ?? 10);
   

Advanced Patterns

1. Dynamic Search Fields: Dynamically add/remove searchable columns based on user roles or settings:

   $searchableColumns = ['posts.title'];
   if (auth()->user()->is_admin) {
       $searchableColumns[] = 'posts.body';
   }
   Post::search($query, $searchableColumns)->get();
   

2. Combining with Scopes: Use the search method alongside custom scopes:

   Post::search($query)
        ->published()
        ->with('author')
        ->get();
   

3. Full-Text Search Optimization:

   • Prepend columns with FULLTEXT index hints for MySQL:

   'columns' => [
       'FULLTEXT(posts.title)',
       'FULLTEXT(posts.body)'
   ]
   

4. Search in Relationships: Search across related models by defining joins and columns:

   'columns' => [
       'comments.body' // Requires a join to comments table
   ],
   'joins' => [
       'comments' => ['comments.post_id', 'posts.id']
   ]
   

Gotchas and Tips

Common Pitfalls

1. MySQL-Only Limitation:

   • The package generates raw SQL queries, so it only works with MySQL.
   • Avoid using on PostgreSQL/SQLite without modification.

2. Case Sensitivity:

   • MySQL’s default LIKE is case-insensitive, but derived columns (e.g., UPPER()) may behave unexpectedly.
   • Use LOWER() for consistent case-insensitive searches:

   'columns' => [
       'LOWER(posts.title)'
   ]
   

3. Reserved Keywords:

   • Column names like order, group, or join may cause SQL syntax errors.
   • Escape or alias them:

   'columns' => [
       'posts.`order`' => 'posts.order'
   ]
   

4. Performance with Large Datasets:

   • Avoid searching on unindexed columns or large text fields.
   • Use FULLTEXT indexes for better performance:

   ALTER TABLE posts ADD FULLTEXT(title, body);
   

5. Join Ambiguity:

   • If tables have identical column names (e.g., created_at), explicitly qualify them:

   'columns' => [
       'posts.created_at',
       'authors.created_at'
   ]
   

Debugging Tips

1. Inspect Generated SQL: Use Laravel’s query logging to debug:

   DB::enableQueryLog();
   Post::search('test')->toSql();
   dd(DB::getQueryLog());
   

2. Validate Join Syntax: Ensure join definitions match your database schema:

   'joins' => [
       'authors' => ['authors.id', 'posts.author_id'] // Correct
       // 'authors' => ['posts.id', 'authors.post_id'] // Incorrect
   ]
   

3. Handle Empty Searches: The package may return all records if $search is empty. Add a guard:

   if (empty($request->search)) {
       return Post::paginate($request->per_page);
   }
   

Extension Points

1. Custom Search Logic: Override the search method in your model:

   public function scopeCustomSearch($query, $searchTerm)
   {
       return $query->where('status', 'active')
                   ->search($searchTerm);
   }
   

2. Add Search to Global Scopes: Attach a global scope for app-wide search:

   class SearchableScope implements Scope
   {
       public function apply(Builder $builder, Model $model, $search)
       {
           return $builder->search($search);
       }
   }
   

3. Extend with Full-Text Search: Combine with Laravel Scout or Algolia for hybrid search:

   Post::search($query)->orWhereHas('scout', function ($q) use ($query) {
       $q->where('body', 'like', "%{$query}%");
   });
   

4. Localization Support: For multilingual apps, add localized columns:

   'columns' => [
       'posts.title',
       'posts.title_translated' => 'posts.title'
   ],
   // Dynamically switch based on locale
   

Configuration Quirks

1. Default Search Behavior:

   • The package uses LIKE with % wildcards by default.
   • To match exact phrases, modify the query:

   Post::where(function ($query) use ($search) {
       foreach ($this->searchable['columns'] as $column) {
           $query->orWhere($column, 'like', "%{$search}%");
       }
   });
   

2. Boolean Search (AND/OR):

   • The package treats searches as OR by default.
   • For AND logic, split the query:

   $terms = explode(' ', $search);
   $query = Post::query();
   foreach ($terms as $term) {
       $query->orWhere(function ($q) use ($term) {
           $q->search($term);
       });
   }