PHP code example of devaction-labs / filterable-package

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

devaction-labs / filterable-package example snippets

use DevactionLabs\FilterablePackage\Filter;

// In your controller
$products = Product::query()
    ->filtrable([
        // Full-text search across multiple columns (PostgreSQL GIN index support)
        Filter::fullText(['name', 'description', 'sku'], 'search')
            ->setFullTextLanguage('portuguese'),

        // Price range filter
        Filter::between('price', 'price_range'),

        // Multiple categories (comma-separated: ?filter[categories]=1,2,3)
        Filter::in('category_id', 'categories'),

        // Relationship filter with eager loading
        Filter::relationship('brand', 'slug', '=', 'brand')
            ->with(),

        // Advanced: Products with ANY of these tags
        Filter::relationship('tags', 'name')
            ->whereAny([
                ['name', '=', 'sale'],
                ['name', '=', 'featured'],
                ['name', '=', 'new'],
            ])
            ->with(),

        // Date filter with automatic Carbon conversion
        Filter::exact('created_at', 'date')
            ->castDate()
            ->endOfDay(),

        // JSON field filtering (PostgreSQL/MySQL)
        Filter::json('attributes', 'color', '=', 'color')
            ->setDatabaseDriver('pgsql'),
    ])
    ->customPaginate('paginate', 20, [
        'per_page' => request('per_page', 20),
        'sort' => request('sort', '-created_at'),
    ]);

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

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use DevactionLabs\FilterablePackage\Traits\Filterable;

class Product extends Model
{
    use Filterable;

    // Optional: Map request parameters to database columns
    protected array $filterMap = [
        'search' => 'name',
        'category' => 'category_id',
    ];

    // Optional: Define sortable columns (prevents SQL injection)
    protected array $allowedSorts = ['name', 'price', 'created_at'];

    // Optional: Default sort
    protected string $defaultSort = '-created_at';
}

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use DevactionLabs\FilterablePackage\Filter;
use App\Models\Product;
use Illuminate\Http\Request;

class ProductController extends Controller
{
    public function index(Request $request)
    {
        $products = Product::query()
            ->filtrable([
                // Text search with custom LIKE pattern
                Filter::like('name', 'search')
                    ->setLikePattern('{{value}}%'), // Starts with search

                // Price range filter
                Filter::between('price', 'price_range'),

                // Date filter with automatic time handling
                Filter::exact('created_at', 'date')
                    ->castDate()
                    ->endOfDay(),

                // JSON field filtering
                Filter::json('attributes', 'specs.color', 'LIKE', 'color')
                    ->setDatabaseDriver('mysql'),

                // Relationship with eager loading
                Filter::relationship('category', 'slug', '=', 'category')
                    ->with(),

                // Full-text search across multiple columns
                Filter::fullText(['name', 'description'], 'q')
                    ->setFullTextLanguage('portuguese'),
            ])
            ->customPaginate('paginate', $request->input('per_page', 15), [
                'per_page' => $request->input('per_page', 15),
                'sort' => $request->input('sort', '-created_at'),
            ]);

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

Filter::method($attribute, $requestParameter)
//               ↑              ↑
//        database column   URL param name

Filter::exact('status', 'product_status')

Filter::fullText($columns, $requestParameter)
//                ↑              ↑
//        array or string   URL param name

// Search across multiple columns
Filter::fullText(['title', 'content', 'tags'], 'search')
//                ↑       ↑          ↑         ↑
//            columns to search      request param

// Step 1: Add to config/app.php
'fulltext_language' => env('FULLTEXT_LANGUAGE', 'simple'),

// Step 2: Add to .env file
FULLTEXT_LANGUAGE=portuguese

// Step 3: Use without specifying (automatically uses .env value)
Filter::fullText(['title', 'content'], 'q')
// Uses 'portuguese' from .env automatically

Filter::fullText(['title', 'content'], 'q')
    ->setFullTextLanguage('portuguese')  // Portuguese stemming
//                         ↑
//                  overrides .env setting

Filter::fullText(['name'], 'q')
    ->setFullTextPrefixMatch(false)  // Exact words only (no wildcards)
//                           ↑
//                        true = "test" matches "testing"
//                        false = "test" matches "test" only

// Step 1: Migration - Create search_vector column with GIN index
Schema::table('products', function (Blueprint $table) {
    $table->tsvector('search_vector')->nullable();
});
DB::statement('CREATE INDEX products_search_idx ON products USING GIN(search_vector)');

// Step 2: Use in filter (10-100x faster than regular columns!)
Filter::fullText('search_vector', 'q')
//                ↑
//        pre-computed column (not array)
    ->setDatabaseDriver('pgsql')

// Example usage for case-insensitive search
$filters = [
    Filter::ilike('name', 'search'), // Works across all databases
];

// Filter posts by user name from request parameter
Filter::relationship('user', 'name', '=', 'user_name')
    ->with(); // Eager load user relationship

// Products with tags that are EITHER 'sale' OR 'featured'
Filter::relationship('tags', 'name')
    ->whereAny([
        ['name', '=', 'sale'],
        ['name', '=', 'featured'],
    ])
    ->with(); // ✅ Correct - no setValue() needed

// Users who have BOTH conditions true
Filter::relationship('permissions', 'name')
    ->whereAll([
        ['name', '=', 'edit-posts'],
        ['is_active', '=', true],
    ])
    ->with(); // ✅ Correct - conditions are hardcoded

// Posts that have NO banned tags
Filter::relationship('tags', 'is_banned')
    ->whereNone([
        ['is_banned', '=', true],
    ])
    ->with();

// ✅ CORRECT way to use dynamic values
Filter::relationship('user', 'id')
    ->whereAll([
        ['id', '=', auth()->id()], // Dynamic value in conditions array
        ['active', '=', true],
        ['verified', '=', true],
    ])
    ->with();

// ❌ WRONG - setValue() doesn't work with whereAll/whereAny/whereNone
Filter::relationship('user', 'id')
    ->whereAll([...])
    ->setValue(auth()->id()); // This has no effect!

$receipts = GoodsReceipt::filterable([
    Filter::exact('status'),
    Filter::anyOf([
        Filter::like('receipt_number'),
        Filter::like('invoice_number'),
        Filter::relationship('items.product', 'sku', 'ILIKE'),
    ], 'search'),          // one shared request key
])->customPaginate();

$results = Expense::query()
    ->filtrable([...])
    ->customPaginate('paginate', 15);

// Returns: total, last_page, current_page, per_page, etc.

$results = Expense::query()
    ->filtrable([...])
    ->customPaginate('simple', 15);

// Returns: current_page, per_page, next_page_url, prev_page_url (no total)

$results = Expense::query()
    ->filtrable([...])
    ->customPaginate('cursor', 15);

// Returns: cursor-based navigation (ideal for infinite scroll)

$results = Expense::query()
    ->filtrable([...])
    ->customPaginate('paginate', 15, [
        'per_page' => 15,
        'sort' => '-created_at'
    ]);

protected string $defaultSort = 'amount';
protected array $allowedSorts = ['amount', 'expense_date'];

protected array $filterMap = [
    'display_name' => 'name',
    'date' => 'expense_date',
];

// Create a date filter that will convert string dates to Carbon instances
$dateFilter = Filter::exact('created_at')->castDate();

// Apply to a query
$model->filtrable([$dateFilter]);

// Filter by date with time set to 23:59:59
$dateFilter = Filter::exact('created_at')->castDate()->endOfDay();

// Filter by date with time set to 00:00:00
$dateFilter = Filter::exact('created_at')->castDate()->startOfDay();

// Default (contains): '%value%'
$filter = Filter::like('description', 'search');

// Starts with: 'value%'
$filter = Filter::like('description', 'search')->setLikePattern('{{value}}%');

// Ends with: '%value'
$filter = Filter::like('description', 'search')->setLikePattern('%{{value}}');

// The query will use the appropriate syntax for your database
$filter = Filter::json('attributes', 'user.age', '>', 'min_age');

// Manually specify database driver if needed
$filter = Filter::json('attributes', 'user.age', '>', 'min_age')->setDatabaseDriver('mysql');

// Match if ANY condition is true (OR logic)
$filter = Filter::relationship('user', 'name')
    ->whereAny([
        ['name', '=', 'John'],
        ['email', '=', '[email protected]'],
    ])
    ->with();

// Match if ALL conditions are true (AND logic)
$filter = Filter::relationship('user', 'name')
    ->whereAll([
        ['name', '=', 'John'],
        ['active', '=', true],
    ])
    ->with();

// Match if NONE of the conditions are true (NOT logic)
$filter = Filter::relationship('user', 'name')
    ->whereNone([
        ['banned', '=', true],
        ['deleted', '=', true],
    ])
    ->with();

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Models\Product;
use DevactionLabs\FilterablePackage\Filter;
use Illuminate\Http\Request;

class ProductController extends Controller
{
    public function index(Request $request)
    {
        // Build a complex query using the Filterable trait
        $products = Product::query()
            // Define allowed sort fields and default sort
            ->allowedSorts(['name', 'price', 'created_at'], '-created_at')
            // Define filter field mappings
            ->filterMap([
                'search' => 'name',
                'price_range' => 'price',
                'date' => 'created_at',
                'status_code' => 'status',
            ])
            // Apply filters
            ->filtrable([
                // Basic filters
                Filter::like('name', 'search')
                    ->setLikePattern('{{value}}%'), // Custom LIKE pattern (starts with)
                
                // Numeric range filter
                Filter::between('price', 'price_range'),
                
                // Date filter with Carbon conversion
                Filter::exact('created_at', 'date')
                    ->castDate()
                    ->endOfDay(), // Automatically set time to end of day
                
                // JSON field filtering
                Filter::json('attributes', 'specs.color', 'LIKE', 'color')
                    ->setDatabaseDriver('mysql'),
                
                Filter::json('attributes', 'specs.weight', '>', 'min_weight')
                    ->setDatabaseDriver('mysql'),
                
                // Relationship filter with eager loading
                Filter::relationship('category', 'slug', '=', 'category')
                    ->with(), // Eager load this relationship
                
                // Complex relationship filter with conditional logic
                Filter::relationship('tags', 'name')
                    ->whereAny([
                        ['name', '=', 'featured'],
                        ['name', '=', 'sale'],
                    ])
                    ->with()
                    ->setValue('has_special_tag'), // Custom value for this filter
                
                // Multiple criteria for user permissions
                Filter::relationship('user', 'id')
                    ->whereAll([
                        ['active', '=', true],
                        ['role', '=', 'admin'],
                    ])
                    ->setValue(auth()->id()),
            ])
            // Apply pagination with custom parameters
            ->customPaginate('paginate', $request->input('per_page', 15), [
                'per_page' => $request->input('per_page', 15),
                'sort' => $request->input('sort', '-created_at'),
            ]);

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

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Models\Product;
use DevactionLabs\FilterablePackage\Filter;
use Illuminate\Http\Request;

class ProductController extends Controller
{
    public function index(Request $request)
    {
        $products = Product::query()
            ->filtrable([
                // Search by name (starts with)
                Filter::like('name', 'search')
                    ->setLikePattern('{{value}}%'),

                // Price range
                Filter::between('price', 'price_range'),

                // Specific categories (multiple)
                Filter::in('category_id', 'categories'),

                // Exclude out of stock
                Filter::notEquals('stock_status', 'exclude_status'),

                // Featured products only
                Filter::exact('is_featured', 'featured'),

                // Filter by brand relationship
                Filter::relationship('brand', 'slug', '=', 'brand')
                    ->with(),

                // Products with ANY of these tags
                Filter::relationship('tags', 'name')
                    ->whereAny([
                        ['name', '=', 'sale'],
                        ['name', '=', 'new'],
                        ['name', '=', 'featured'],
                    ])
                    ->with(),

                // Full-text search across multiple fields
                Filter::fullText(['name', 'description', 'sku'], 'q')
                    ->setFullTextLanguage('portuguese'),
            ])
            ->customPaginate(
                $request->input('pagination_type', 'paginate'),
                $request->input('per_page', 20),
                [
                    'per_page' => $request->input('per_page', 20),
                    'sort' => $request->input('sort', '-created_at'),
                ]
            );

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

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Models\Post;
use DevactionLabs\FilterablePackage\Filter;
use Illuminate\Http\Request;

class PostController extends Controller
{
    public function index(Request $request)
    {
        $posts = Post::query()
            ->filtrable([
                // Search in title and content
                Filter::fullText(['title', 'content', 'excerpt'], 'search')
                    ->setFullTextLanguage('portuguese')
                    ->setFullTextPrefixMatch(true),

                // Filter by status
                Filter::exact('status', 'status'),

                // Published date range
                Filter::between('published_at', 'date_range'),

                // Posts by specific author
                Filter::relationship('author', 'id', '=', 'author_id')
                    ->with(),

                // Posts with ALL these categories
                Filter::relationship('categories', 'slug')
                    ->whereAll([
                        ['slug', '=', $request->input('filter.primary_category')],
                        ['is_active', '=', true],
                    ])
                    ->with(),

                // Posts tagged with ANY of these tags
                Filter::relationship('tags', 'slug', '=', 'tags')
                    ->with(),

                // Only published posts
                Filter::isNotNull('published_at', 'published'),

                // Featured posts
                Filter::exact('is_featured', 'featured'),
            ])
            ->customPaginate('cursor', 10); // Use cursor for better performance

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

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Models\User;
use DevactionLabs\FilterablePackage\Filter;
use Illuminate\Http\Request;

class UserController extends Controller
{
    public function index(Request $request)
    {
        $users = User::query()
            ->filtrable([
                // Search by name or email
                Filter::like('name', 'search'),
                Filter::like('email', 'email'),

                // Active users only
                Filter::exact('is_active', 'active'),

                // Users with specific role
                Filter::relationship('roles', 'name', '=', 'role')
                    ->with(),

                // Users with ALL $request->input('sort', 'name'),
            ]);

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

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Models\Product;
use DevactionLabs\FilterablePackage\Filter;
use Illuminate\Http\Request;

class ProductController extends Controller
{
    public function index(Request $request)
    {
        $products = Product::query()
            ->filtrable([
                // JSON field filtering - exact match
                Filter::json('specifications', 'dimensions.width', '=', 'width')
                    ->setDatabaseDriver('pgsql'),

                // JSON field - greater than
                Filter::json('specifications', 'weight', '>', 'min_weight')
                    ->setDatabaseDriver('pgsql'),

                // JSON field - LIKE search
                Filter::json('specifications', 'material', 'LIKE', 'material')
                    ->setDatabaseDriver('pgsql'),

                // Multiple JSON paths
                Filter::json('metadata', 'seo.keywords', 'LIKE', 'keywords')
                    ->setDatabaseDriver('mysql'),
            ])
            ->customPaginate('paginate', 20);

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

// setValue() has no effect here because conditions are already hardcoded
Filter::relationship('tags', 'name')
    ->whereAny([
        ['name', '=', 'featured'],
        ['name', '=', 'sale'],
    ])
    ->setValue('custom_value') // ❌ This doesn't work
    ->with();

// Remove setValue() - the conditions already define what to match
Filter::relationship('tags', 'name')
    ->whereAny([
        ['name', '=', 'featured'],
        ['name', '=', 'sale'],
    ])
    ->with(); // ✅ This works correctly

// Trying to use setValue() for dynamic values
Filter::relationship('user', 'id')
    ->whereAll([
        ['active', '=', true],
        ['role', '=', 'admin'],
    ])
    ->setValue(auth()->id()); // ❌ This doesn't filter by user ID

// Include dynamic values directly in the conditions array
Filter::relationship('user', 'id')
    ->whereAll([
        ['id', '=', auth()->id()], // ✅ Dynamic value here
        ['active', '=', true],
        ['role', '=', 'admin'],
    ])
    ->with(); // ✅ This works correctly

// Relationship will cause N+1 queries
Filter::relationship('category', 'slug', '=', 'category'); // ❌ Missing ->with()

// Eager load the relationship to avoid N+1
Filter::relationship('category', 'slug', '=', 'category')
    ->with(); // ✅ Eager loads the relationship

// Database driver not set - may not work correctly
Filter::json('data', 'user.name', '=', 'name'); // ❌ Driver not set

// Always set the database driver for JSON filters
Filter::json('data', 'user.name', '=', 'name')
    ->setDatabaseDriver('pgsql'); // ✅ Driver specified

// Between expects array but gets string from request
// Request: ?filter[price_range]=100
$filter = Filter::between('price', 'price_range'); // ❌ Will fail

// Request should send comma-separated values
// Request: ?filter[price_range]=100,500
$filter = Filter::between('price', 'price_range'); // ✅ Automatically converts "100,500" to [100, 500]

// String date won't work with endOfDay()
Filter::exact('created_at', 'date')
    ->endOfDay(); // ❌ Expects Carbon instance

// Use castDate() to convert string to Carbon
Filter::exact('created_at', 'date')
    ->castDate() // ✅ Converts to Carbon first
    ->endOfDay(); // ✅ Now this works

   Filter::relationship('user', 'id')->with() // Eager load
   

   Filter::json('data', 'path')->setDatabaseDriver(config('database.default'))
   

   Filter::fullText(['title', 'content'], 'search')
       ->setFullTextLanguage('portuguese')
   

   // Better performance
   Filter::relationship('user', 'email')
       ->whereAll([
           ['email', '=', '[email protected]'],
           ['active', '=', true],
       ])
   

   ->customPaginate('cursor', 20) // Better performance than 'paginate'
   

   protected array $allowedSorts = ['name', 'created_at', 'price'];
   

// Filters for Order
// Add to your controller: use DevactionLabs\FilterablePackage\Filter;

$orders = Order::filterable([
    Filter::in('status'),                          // accepts: ?filter[status]=pending,paid
    Filter::between('total')->castDate(),           // range: ?filter[total]=100,500
    Filter::between('created_at')->castDate(),      // date range
    Filter::exact('user_id'),                      // foreign key exact match
    Filter::relationship('items', 'id'),           // filter by items relationship
])->customPaginate('paginate', 15);

bash
# Search active users
GET /api/users?filter[search]=john&filter[active]=1

# Users with admin role
GET /api/users?filter[role]=admin&filter[verified]=1

# Users registered this year
GET /api/users?filter[registration_date]=2024-01-01,2024-12-31

# Exclude specific users
GET /api/users?filter[exclude_users]=1,2,3&sort=name

bash
php artisan vendor:publish --tag=filterable-mcp

bash
php artisan filterable:mcp