Spatie Laravel Query Builder – Filter, sort, and include relationships in Eloquent queries

Spatie Laravel Query Builder is a package that allows you to easily build Eloquent queries by applying filters, sorting, and including relationships. This package simplifies the process of handling complex query parameters from API requests, enabling cleaner and more maintainable code.

Key Features of Spatie Laravel Query Builder:

Filtering: Easily apply filters to your Eloquent queries based on request parameters.
Sorting: Support for dynamic sorting of query results.
Includes: Include related models and eager load relationships to optimize database queries.
Customizable: Extend and customize query building logic to fit your application’s needs.
Validation: Validate query parameters to ensure they conform to expected formats.

Installation

To get started with Spatie Laravel Query Builder, follow these steps:

Require the Package: Install the package via Composer:

composer require spatie/laravel-query-builder

1

composer require spatie/laravel-query-builder
Publish Configuration (Optional): You can publish the configuration file using:

php artisan vendor:publish --provider="Spatie\QueryBuilder\QueryBuilderServiceProvider"

1

php artisan vendor:publish --provider="Spatie\QueryBuilder\QueryBuilderServiceProvider"

Setting Up the Query Builder

Basic Usage: To use the query builder in a controller, you first import the QueryBuilder class and then apply it to your Eloquent model. Here’s an example with a Post model:

namespace App\Http\Controllers;

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

class PostController extends Controller
{
    public function index(Request $request)
    {
        $posts = QueryBuilder::for(Post::class)
            ->allowedFilters(['title', 'created_at'])
            ->allowedSorts('title', 'created_at')
            ->allowedIncludes('comments', 'author')
            ->get();

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

namespace App\Http\Controllers;

use App\Models\Post;

use Spatie\QueryBuilder\QueryBuilder;

use Illuminate\Http\Request;

class PostController extends Controller

{

public function index(Request $request)

{

$posts = QueryBuilder::for(Post::class)

->allowedFilters(['title', 'created_at'])

->allowedSorts('title', 'created_at')

->allowedIncludes('comments', 'author')

->get();

return response()->json($posts);

}

Allowed Filters: Specify which filters are allowed for your queries using allowedFilters(). You can filter based on fields in the model, and you can also create custom filters.

->allowedFilters(['title', 'created_at'])

1

->allowedFilters(['title', 'created_at'])
Allowed Sorts: Define which fields can be sorted using allowedSorts():

->allowedSorts('title', 'created_at')

1

->allowedSorts('title', 'created_at')
Including Relationships: Use allowedIncludes() to specify which relationships can be eagerly loaded:

->allowedIncludes('comments', 'author')

1

->allowedIncludes('comments', 'author')

Custom Filters

You can also create custom filters to handle more complex query logic. Here’s how to create a custom filter for filtering posts by a specific keyword in the title:

Create a Filter Class: Use the php artisan make:filter command to create a custom filter:

php artisan make:filter TitleFilter

1

php artisan make:filter TitleFilter

Implement the Filter Logic: In your filter class, implement the apply() method:

namespace App\Filters;

use Illuminate\Database\Eloquent\Builder;
use Spatie\QueryBuilder\Filters\Filter;

class TitleFilter implements Filter
{
    public function __invoke(Builder $query, $value, string $property)
    {
        return $query->where('title', 'like', "%{$value}%");
    }
}

namespace App\Filters;

use Illuminate\Database\Eloquent\Builder;

use Spatie\QueryBuilder\Filters\Filter;

class TitleFilter implements Filter

{

public function __invoke(Builder $query, $value, string $property)

{

return $query->where('title', 'like', "%{$value}%");

}

Register the Custom Filter: Register your custom filter in the controller:

use App\Filters\TitleFilter;

$posts = QueryBuilder::for(Post::class)
    ->allowedFilters([new TitleFilter(), 'created_at'])
    // other query builder methods...
    ->get();

use App\Filters\TitleFilter;

$posts = QueryBuilder::for(Post::class)

->allowedFilters([new TitleFilter(), 'created_at'])

// other query builder methods...

->get();

Conclusion

Spatie Laravel Query Builder is a powerful tool for efficiently building Eloquent queries with a focus on filtering, sorting, and including relationships. It helps keep your code clean and maintainable, especially when dealing with complex query logic.

Additional Considerations

Documentation: For more detailed information, advanced features, and configuration options, refer to the official Spatie Laravel Query Builder documentation.
Performance: Always test and optimize your queries, especially when dealing with large datasets or complex relationships.