README

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

Operator	Query String	Description
`like` (default)	`title=Laravel`	Filter data with LIKE "%Laravel%"
`eq`	`status=published&status_op=eq`	Filter where status = 'published'
`gt`	`created_at=2023-01-01&created_at_op=gt`	Filter where created_at > '2023-01-01'
`gte`	`created_at=2023-01-01&created_at_op=gte`	Filter where created_at >= '2023-01-01'
`lt`	`created_at=2023-01-01&created_at_op=lt`	Filter where created_at < '2023-01-01'
`lte`	`created_at=2023-01-01&created_at_op=lte`	Filter where created_at <= '2023-01-01'
`between`	`created_at=2023-01-01,2023-12-31&created_at_op=between`	Filter data within range
`notIn`	`status=draft,pending&status_op=notIn`	Exclude values in the list
`in`	`status=draft,pending&status_op=in`	Filter values in the list
`null`	`deleted_at=1&deleted_at_op=null`	Filter where field is NULL
`notNull`	`deleted_at=1&deleted_at_op=notNull`	Filter where field is NOT NULL

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.

laravelwakeup / filter-sort

Maintainers

Details