Download the PHP package devaction-labs/filterable-package without Composer

Filterable Package

A Laravel package for filterable traits and classes. This package provides powerful, dynamic query filtering capabilities directly from incoming requests, especially useful when developing flexible and dynamic APIs.

Quick Example

Example Request:

Generated SQL:

• ✅ Optimized WHERE clauses
• ✅ Automatic JOIN for relationships
• ✅ Eager loading to prevent N+1
• ✅ PostgreSQL full-text search with GIN indexes (10-100x faster!)
• ✅ Pagination with sort support

Features

• Easy Integration: Apply the Filterable trait to your Eloquent models.
• Comprehensive Filters: Support for 15+ filter types including exact, like, ilike, in, between, greater/less than, negation filters (notEquals, notIn, notLike), null checks (isNull, isNotNull), and text pattern filters (startsWith, endsWith).
• Full-Text Search: Intelligent full-text search with automatic database adapter (PostgreSQL native search with GIN indexes, MySQL/SQLite fallback).
• Database Compatibility: Database-specific optimizations for PostgreSQL, MySQL, and SQLite.
• Dynamic Sorting: Customize sorting behavior directly from requests.
• Relationship Filters: Use advanced conditional logic like whereAny, whereAll, and whereNone for relational queries.
• JSON Support: Directly filter JSON columns with dot-notation.
• Performance Optimizations: Built-in caching and efficient query construction.
• Date Handling: Smart handling of date fields with Carbon integration.

Available Filter Types

And more: notEquals, notLike, endsWith, isNull, gt, lt

📚 See Complete Reference for detailed parameter explanations

📖 Documentation

• Complete Filter Reference - Detailed explanation of every filter type with all parameters explained
• Practical Examples - Real-world use cases and code examples
• README - Quick start and overview (this file)

Installation

Requirements:

• PHP 8.3, 8.4, or 8.5
• Laravel 11 or 12

Getting Started

1️⃣ Add the Filterable Trait to Your Model

2️⃣ Use Filters in Your Controller

Example Request:

Quick Filter Reference

📚 For detailed explanations of all parameters, see FILTER_REFERENCE.md

Understanding Parameters

Every filter follows this pattern:

Example:

Request:

SQL:

Available Filters

Direct Filters

Basic Comparison Filters

• Exact Match: Filter::exact('status', 'status')
• Not Equals: Filter::notEquals('status', 'exclude_status')
• Greater Than: Filter::gt('amount', 'min_amount')
• Greater Than or Equal: Filter::gte('amount', 'min_amount')
• Less Than: Filter::lt('amount', 'max_amount')
• Less Than or Equal: Filter::lte('amount', 'max_amount')
• Between: Filter::between('created_at', 'date_range')

Text Search Filters

• LIKE Match: Filter::like('description', 'search')
• Case-Insensitive LIKE: Filter::ilike('description', 'search') (Database-specific)
• NOT LIKE: Filter::notLike('description', 'exclude_text')
• Starts With: Filter::startsWith('name', 'name_prefix')
• Ends With: Filter::endsWith('email', 'email_suffix')
• Full-Text Search: Filter::fullText(['title', 'content'], 'search') (Database-specific)

Full-Text Search

📚 Complete Full-Text Search Documentation - Includes GIN index setup, performance tips, and all configuration options

The fullText() filter provides powerful text search capabilities that automatically adapt to your database.

Syntax:

Parameter 1: Columns

• Array: Search multiple columns ['title', 'content', 'tags']
• String: Single column 'name' or pre-computed 'search_vector'

Parameter 2: Request Parameter

• URL parameter name (e.g., 'search', 'q')
• Defaults to 'search' if omitted

Database Strategies:

• PostgreSQL: Native full-text with to_tsvector, to_tsquery, GIN indexes (10-100x faster)
• MySQL/SQLite: Falls back to LIKE across multiple columns

Basic Examples:

Request: GET /api/posts?filter[search]=laravel framework

Configuration Methods:

1. Language (PostgreSQL only):

Option A: Set via Environment Variable (Recommended)

Option B: Set Explicitly (Overrides .env)

Available: 'simple', 'english', 'portuguese', 'spanish', 'french', etc.

2. Prefix Matching:

High-Performance Setup (PostgreSQL with GIN Index):

Request: GET /api/products?filter[q]=macbook pro

Performance: 5ms vs 500ms on 1M rows (100x faster!)

💡 See FILTER_REFERENCE.md for complete GIN index setup with triggers

List and Array Filters

• IN Clause: Filter::in('category_id', 'categories')
• NOT IN Clause: Filter::notIn('status', 'exclude_statuses')

Null Value Filters

• Is Null: Filter::isNull('deleted_at', 'show_deleted')
• Is Not Null: Filter::isNotNull('email_verified_at', 'verified_only')

Database-Specific Behavior

The ilike() filter automatically adapts to your database:

• PostgreSQL: Uses native ILIKE operator
• SQLite: Falls back to LIKE (case-sensitive)
• MySQL: Uses LOWER() function for case-insensitive comparison

JSON Filters

• Exact Match: Filter::json('data', 'user.name', '=', 'user_name')
• LIKE Match: Filter::json('data', 'user.name', 'LIKE', 'user_name')

Relationship Filters

Simple Relationship Filter

Request: ?filter[user_name]=John

Relationship with whereAny (OR logic)

Relationship with whereAll (AND logic)

Relationship with whereNone (NOT logic)

Using Dynamic Values in Relationship Conditions

OR Groups Across Columns and Relationships — Filter::anyOf()

Every filter in filterable([...]) is combined with AND. Filter::anyOf() groups filters with OR inside a single parenthesised condition, and the children may mix direct columns and relationship filters. This is the way to build one search box that matches an own-table column or a related-model column:

Request: ?filter[status]=open&filter[search]=ABC

The alternatives are grouped, so a row that only matches inside the group can never escape the other filters (e.g. a status != 'open' receipt will not leak). Omit the second argument to OR filters that each read their own request key. See the Filter Reference for both forms and performance notes.

Customizing Pagination and Sorting

The package provides flexible pagination options through the customPaginate method, supporting three pagination types:

Standard Pagination (with total count)

Simple Pagination (without total count - better performance)

Cursor Pagination (most performant for large datasets)

Custom Parameters

You can pass custom data to append to pagination links:

Sorting:

• - (minus) prefix indicates descending sorting (e.g., -amount)
• Ascending sort uses the field name directly (e.g., amount)

Defining Default Sorting and Allowed Sorts in Model:

Security & Hardening (sort / per_page)

Since sort and per_page come from the request, the package validates them:

• Always configure allowedSorts(). With no allow-list, any column may be used as a sort key, which lets a caller order by a hidden column and infer its values. Request sort keys are always validated to be plain column identifiers (this alone closes a PostgreSQL ->-based SQL injection), but the allow-list is what prevents column enumeration.
• per_page is clamped to [1, 100]. Change the ceiling with config('filterable.max_per_page') or a per-model protected int $maxPerPage. An explicit value you pass in code (e.g. customPaginate('paginate', 250)) is not capped — only request-supplied values are.
• Strict sorting (opt-in): set config('filterable.strict_sorts') to true to reject any request sort when a model has no allowedSorts() list.

Invalid input (?sort[]=x, ?sort=col->x, an unlisted sort) throws a catchable InvalidArgumentException — render it as a 422/400 in your exception handler.

Custom Filter Mapping

Easily map request parameters to database columns:

Now, using the parameter filter[display_name]=John will filter on the name column.

Advanced Features

Date Handling

The Filterable package provides sophisticated date handling capabilities:

You can also specify if you want to compare with the start or end of the day:

Custom LIKE Patterns

Customize the pattern used for LIKE filters to match your search requirements:

JSON Field Filtering with Database-Specific Optimizations

The package automatically applies the correct JSON extraction syntax based on your database:

Advanced Relationship Filtering with Conditional Logic

Apply complex conditions to your relationship filters:

Performance Optimizations

The Filterable trait includes several performance optimizations:

• Efficient caching of attribute and relationship validations
• Optimized handling of relationship filters
• Smart deduplication of eager-loaded relationships
• Specialized handling for simple equality relationship filters

These optimizations are automatically applied when you use the trait, ensuring your filterable queries remain performant even with complex filter combinations.

Complete Usage Example

Here's a comprehensive example showing how to use multiple features together:

Supported Databases for JSON Filters

• MySQL
• PostgreSQL
• SQLite

The package automatically detects the database driver from your configuration.

Practical Examples

Example 1: E-commerce Product Filtering

Example Requests:

Example 2: Blog Post Filtering

Example Requests:

Example 3: User Management with Permissions

Example Requests:

Example 4: Advanced JSON Filtering

Example Requests:

Common Mistakes and How to Avoid Them

❌ Mistake 1: Using setValue() with whereAny/whereAll/whereNone

Wrong:

Correct:

❌ Mistake 2: Dynamic Values in whereAll

Wrong:

Correct:

❌ Mistake 3: Forgetting ->with() for Eager Loading

Wrong:

Correct:

❌ Mistake 4: Wrong Database Driver for JSON Filters

Wrong:

Correct:

❌ Mistake 5: Using Between with Non-Array Values

Wrong:

Correct:

❌ Mistake 6: Not Using castDate() for Date Filters

Wrong:

Correct:

✅ Best Practices

1. Always use ->with() for relationships you need in the response

2. Set database driver for JSON filters

3. Use full-text search for better search experience

4. Prefer whereAny/whereAll over multiple separate relationship filters

5. Use cursor pagination for large datasets

6. Define allowedSorts in your model to prevent SQL injection

AI Agent Integration (MCP)

Since v2.2.0, the package ships with a Model Context Protocol (MCP) server that lets AI coding assistants (Claude Code, Cursor, Windsurf, etc.) understand your models and generate correct filter code automatically — without you having to explain the API.

Setup

Publish the MCP configuration to your project root:

This creates a .mcp.json file:

AI agents that support MCP will automatically discover this server and connect to it when you open the project. No further configuration needed.

What the agent can do

Once connected, the agent has access to four tools:

Example interaction

After setup, you can ask the agent naturally:

"Generate the filter array for the Order model"

The agent will:

1. Call get_model_schema(Order) to inspect columns and relationships
2. Call generate_filters(Order) to produce the code
3. Return something like:

Manual start

The MCP server runs over stdio and is started automatically by supporting editors. To start it manually:

IDE support

Testing

Contributing

Please see CONTRIBUTING.md for details.

Security

If you discover any security related issues, please email [email protected] instead of using the issue tracker.

Credits

• DevAction Labs
• Alex Nogueira

License

The MIT License (MIT). Please see License File for more information.