Filter Sort Scope for Laravel

Table of Contents

• Introduction
• Requirements
• Installation & Configuration
• Basic Usage
• Available Operators
• Examples
• JavaScript Usage
• License

Introduction

This package provides FilterTrait and SortTrait to help you filter and sort data dynamically with various operators in Laravel Eloquent.

Requirements

• PHP >= 8.0
• Laravel >= 8.0

I. Installation & Configuration

1. Install

composer require laravelwakeup/filter-sort

2. Publish Configuration

php artisan vendor:publish --tag=laravel-filter-sort-config

After running the command above, the laravel-filter-sort.php config file will be created in your config/ directory. You can adjust the following settings:

return [
    // Example: status_op=eq, status_sort=desc
    'prefix' => '_op',
    'sort_field_suffix' => '_sort'
];

II. Basic Usage

1. Add Traits to Your Model

use LaravelWakeUp\FilterSort\Traits\FilterTrait;
use LaravelWakeUp\FilterSort\Traits\SortTrait;

class Post extends Model
{
    use FilterTrait, SortTrait;
    
    // Optional: Restrict which fields can be filtered
    protected array $allowedFilters = ['title', 'created_at', 'status', 'deleted_at'];

    // Optional: Restrict which fields can be sorted
    protected array $allowedSorts = ['id', 'created_at'];
}

Note: By default, if you don't define or set empty arrays for $allowedFilters and $allowedSorts, the package will allow filtering and sorting on all table fields.

2. Use in Controller

$posts = Post::query()
    ->filter(request())
    ->sort(request())
    ->get();

III. Available Operators

IV. Examples

1. Sorting

The package provides a simple and flexible way to sort your data. Sorting is applied only when sort parameters are present in the request.

# Sort by single field
/posts?id_sort=desc

# Sort by multiple fields (applies in order of appearance)
/posts?created_at_sort=desc&id_sort=asc

# Combine with filters
/posts?title=Laravel&status=published&status_op=eq&created_at_sort=desc&id_sort=asc

1.1 Sorting Parameters

For any field you want to sort by (e.g., id, created_at, title), append _sort to the field name:

• {field}_sort: Set the sort direction
  • asc for ascending order (default if invalid value provided)
  • desc for descending order

1.2 Multiple Sort Example

// Sort by created_at DESC, then by id ASC
/posts?created_at_sort=desc&id_sort=asc

// Sort by status DESC, created_at DESC, and id ASC
/posts?status_sort=desc&created_at_sort=desc&id_sort=asc

1.3 Restrict Sortable Fields

You can customize sorting behavior in your model:

protected array $allowedSorts = ['id', 'created_at', 'title', 'status'];

1.4 Customize Sort Field Suffix

You can change the default _sort suffix by publishing the config file and modifying the sort_field_suffix value:

// config/laravel-filter-sort.php
return [
    'sort_field_suffix' => '_sort'  // Change this to your preferred suffix
];

Note: Sorting is only applied when sort parameters are provided in the request. The order of sorting follows the order of parameters in the URL.

2. Search

2.1 Basic search

# Fuzzy search (LIKE)
/posts?title=Laravel

# Exact match (Equal)
/posts?status=published&status_op=eq

# NULL check
/posts?deleted_at=1&deleted_at_op=null

# NOT NULL check
/posts?deleted_at=1&deleted_at_op=notNull

2.2 Range Search

# Greater than
/posts?created_at=2023-01-01&created_at_op=gt

# Between range
/posts?created_at=2023-01-01,2023-12-31&created_at_op=between

Use two separate inputs for the start and end of the range. These inputs must have the suffix _start_range and _end_range, which are fixed and required for the range query to work correctly.

<form method="GET" action="/accounts">
    <input type="text" name="price_start_range" placeholder="Start Price">
    <input type="text" name="price_end_range" placeholder="End Price">
    <button type="submit">Search</button>
</form>

Note: The suffixes _start_range and _end_range are fixed and must be used exactly as shown to ensure the range queries are processed correctly.

2.3 List Search

# Filter by list (IN)
/posts?status=draft,pending&status_op=in

# Exclude list (NOT IN)
/posts?status=draft,pending&status_op=notIn

2.4 Multi-Column Search

The multi-column search feature allows you to search across multiple columns using a single search term. This is useful for implementing a search bar that can search across different fields in your database.

Define Multi-Column Search Configuration in Your Model

In your Eloquent model, define a multiColumnSearch property to specify which fields should be included in the search and the operators to use.

protected array $multiColumnSearch = [
    'search_field' => 'search_txt', // The request parameter to use for the search term
    'fields' => [
        'username' => 'like',
        'server' => 'eq',
        // Add more fields as needed
    ],
];

Suppose you have a search input in your form with the name search_txt. When the form is submitted, the filter scope will automatically apply the search term to the specified fields using the defined operators.

<form method="GET" action="/accounts">
    <input type="text" name="search_txt" placeholder="Search...">
    <button type="submit">Search</button>
</form>

This will search the username and server fields in the accounts table using the specified operators.

V. JavaScript Usage

1. Using with qs library

// Installation
// npm install qs
// yarn add qs

// Import
import qs from 'qs';
// or
const qs = require('qs');

// Example filters object
const filters = {
    // Normal filter
    name: 'John',
    name_op: 'like',
    
    // Filter with IN operator
    status: ['active', 'pending'],
    status_op: 'in',
    
    // Filter with BETWEEN operator
    created_at: ['2023-01-01', '2023-12-31'],
    created_at_op: 'between',
    
    // Filter with NULL operator
    deleted_at: '1',
    deleted_at_op: 'null',
    
    // Multiple field sorting
    created_at_sort: 'desc',
    id_sort: 'asc'
};

// Convert object to query string
const queryString = qs.stringify(filters, {
    arrayFormat: 'comma',    // Convert arrays to comma-separated strings
    encode: false            // Don't encode special characters
});
// Result: name=John&name_op=like&status=active,pending&status_op=in&created_at=2023-01-01,2023-12-31&created_at_op=between&deleted_at=1&deleted_at_op=null&created_at_sort=desc&id_sort=asc

// API call with Axios
axios.get(`/api/posts?${queryString}`);

// API call with Fetch
fetch(`/api/posts?${queryString}`);

// API call with jQuery
$.get(`/api/posts?${queryString}`);

// Parse query string back to object
const url = window.location.search; // ?name=John&name_op=like...
const parsed = qs.parse(url, { 
    ignoreQueryPrefix: true,
    comma: true  // Parse comma-separated strings back to arrays
});

console.log(parsed);
// {
//     name: 'John',
//     name_op: 'like',
//     status: ['active', 'pending'],
//     status_op: 'in',
//     created_at: ['2023-01-01', '2023-12-31'],
//     created_at_op: 'between',
//     deleted_at: '1',
//     deleted_at_op: 'null',
//     created_at_sort: 'desc',
//     id_sort: 'asc'
// }

2. Using URLSearchParams (Browser built-in)

// Create a new URLSearchParams instance
const params = new URLSearchParams();

// Add normal filter
params.append('name', 'John');
params.append('name_op', 'like');

// Add filter with IN operator
params.append('status', 'active,pending');  // Use string directly instead of array.join()
params.append('status_op', 'in');

// Add filter with BETWEEN operator
params.append('created_at', '2023-01-01,2023-12-31');  // Use string directly
params.append('created_at_op', 'between');

// Add filter with NULL operator
params.append('deleted_at', '1');
params.append('deleted_at_op', 'null');

// Add sorting
params.append('created_at_sort', 'desc');
params.append('id_sort', 'asc');

// Convert to query string and decode it
const queryString = decodeURIComponent(params.toString());
// Result: name=John&name_op=like&status=active,pending&status_op=in&created_at=2023-01-01,2023-12-31&created_at_op=between&deleted_at=1&deleted_at_op=null&created_at_sort=desc&id_sort=asc

// API calls
// With Fetch
fetch(`/api/posts?${queryString}`);

// With Axios
axios.get(`/api/posts?${queryString}`);

// With jQuery
$.get(`/api/posts?${queryString}`);

VI. License

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