A query database collection for use with Laravel Pipeline

This package contains a collection of class that can be used with Laravel Pipeline. Let's see below queries:

// users?name=Baro&is_admin=1&created_at_from=2022-06-01&created_at_to=2022-06-31
$users = User::query()
    ->when($request->name ?? null, function($query, $name) {
        $query->where('name', 'like', "%$name%");
    })
    ->when($request->is_admin ?? null, function($query, $isAdmin) {
        $query->where('is_admin', $isAdmin ? 1 : 0);
    })
    ->when($request->created_at_from ?? null, function($query, $date) {
        $query->where('created_at', '>=', $date);
    })
    ->when($request->created_at_to ?? null, function($query, $date) {
        $query->where('created_at', '<=', $date);
    })
    ->get();

As you all can see, it's obviously that filter conditions will continue to grow as well as the duplication of same filter for other queries. We can use Laravel Pipeline combine with some pre-made queries to refactor this

use Baro\PipelineQueryCollection;

// users?name=Baro&is_admin=1&created_at_from=2022-06-01&created_at_to=2022-06-31
$users = Users::query()->filter([
    PipelineQueryCollection\RelativeFilter::make('name'),
    PipelineQueryCollection\BooleanFilter::make('is_admin'),
    PipelineQueryCollection\DateFromFilter::make('created_at'),
    PipelineQueryCollection\DateToFilter::make('created_at'),
])
->get();

Table of Contents

• A query database collection for use with Laravel Pipeline
• Table of Contents
• Installation
• Usage
  • Preparing your model
  • Feature
    • Bitwise filter
    • Boolean filter
    • Date From filter
    • Date To filter
    • Exact filter
    • Relation filter
    • Relative filter
    • Scope filter
    • Trash filter
    • Sort
  • Detector
  • Custom search column
  • Custom search value
  • Extend filter
• Testing
• Contributing
• Security Vulnerabilities
• Credits
• License

Installation

Install the package via composer:

composer require l3aro/pipeline-query-collection

Optionally, you can publish the config file with:

php artisan vendor:publish --tag="pipeline-query-collection-config"

This is the contents of the published config file:

return [
    // key to detect param to filter
    'detect_key' => env('PIPELINE_QUERY_COLLECTION_DETECT_KEY', ''),

    // type of postfix for date filters
    'date_from_postfix' => env('PIPELINE_QUERY_COLLECTION_DATE_FROM_POSTFIX', 'from'),
    'date_to_postfix' => env('PIPELINE_QUERY_COLLECTION_DATE_TO_POSTFIX', 'to'),

    // default motion for date filters
    'date_motion' => env('PIPELINE_QUERY_COLLECTION_DATE_MOTION', 'find'),
];

Usage

Preparing your model

To use this collection with a model, you should implement the following interface and trait:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Baro\PipelineQueryCollection\Concerns\Filterable;
use Baro\PipelineQueryCollection\Contracts\CanFilterContract;

class YourModel extends Model implements CanFilterContract
{
    use Filterable;

    public function getFilters(): array
    {
        return [
            // the filter and sorting that your model need
        ];
    }
}

After setup your model, you can use scope filter on your model like this

YourModel::query()->filter()->get();

You can also override the predefined filter lists in your model like this

YourModel::query()->filter([
    // the custom filter and sorting that your model need
])
->paginate();

Feature

Here the use all filter and sort in the collection

Bitwise filter

use Baro\PipelineQueryCollection\BitwiseFilter;

// users?permission[0]=2&permission[1]=4
User::query()->filter([
    BitwiseFilter::make('permission'), // where permission & 6 = 6
]);

Boolean filter

use Baro\PipelineQueryCollection\BooleanFilter;

// users?is_admin=1
User::query()->filter([
    BooleanFilter::make('is_admin'), // where is_admin = 1
]);

Date From filter

use Baro\PipelineQueryCollection\DateFromFilter;
use Baro\PipelineQueryCollection\Enums\MotionEnum;

// users?updated_at_from=2022-05-31
User::query()->filter([
    DateFromFilter::make('updated_at'), // where updated_at >= 2022-05-31
    DateFromFilter::make('updated_at', MotionEnum::TILL), // where updated_at > 2022-05-31
    // you can config default motion behavior and the postfix `from` in the config file
]);

Date To filter

use Baro\PipelineQueryCollection\DateToFilter;
use Baro\PipelineQueryCollection\Enums\MotionEnum;

// users?updated_at_to=2022-05-31
User::query()->filter([
    DateToFilter::make('updated_at'), // where updated_at <= 2022-05-31
    DateToFilter::make('updated_at', MotionEnum::TILL), // where updated_at < 2022-05-31
    // you can config default motion behavior and the postfix `to` in the config file
]);

Exact filter

use Baro\PipelineQueryCollection\ExactFilter;

// users?id=4
User::query()->filter([
    ExactFilter::make('id'), // where id = 4
]);

Relation filter

use Baro\PipelineQueryCollection\RelationFilter;

// users?roles_id[0]=1&roles_id[1]=4
User::query()->filter([
    RelationFilter::make('roles', 'id'), // where roles.id in(1,4)
]);

Relative filter

use Baro\PipelineQueryCollection\RelativeFilter;
use Baro\PipelineQueryCollection\Enums\WildcardPositionEnum;

// users?name=Baro
User::query()->filter([
    RelativeFilter::make('name'), // where('name', 'like', "%Baro%")
    RelativeFilter::make('name', WildcardPositionEnum::LEFT), // where('name', 'like', "%Baro")
    RelativeFilter::make('name', WildcardPositionEnum::RIGHT), // where('name', 'like', "Baro%")
]);

Scope filter

// users?search=Baro

// User.php
public function scopeSearch(Builder $query, string $keyword)
{
    return $query->where(function (Builder $query)  use ($keyword) {
        $query->where('id', $keyword)
            ->orWhere('name', 'like', "%{$keyword}%");
    });
}

// Query
use Baro\PipelineQueryCollection\ScopeFilter;

User::query()->filter([
    ScopeFilter::make('search'), // where (`id` = 'Baro' or `name` like '%Baro%')
]);

Trash filter

When using Laravel's soft delete, you can use the pipe TrashFilter to query these models. The default query name is trashed, and filters responds to particular values:

• with: the query should be ?trashed=with to include soft deleted records to the result set
• only: the query should be ?trashed=only to return only soft deleted records to the result set
• any other value, or completely remove trashed from request query will return only records that are not soft deleted in the result set

You can change query name trashed by passing your custom name to the TrashFilter constructor

use Baro\PipelineQueryCollection\TrashFilter;


// ?removed=only
User::query()->filter([
   TrashFilter::make('removed'), // where `deleted_at` is not null
]);

Sort

use Baro\PipelineQueryCollection\ScopeFilter;

// users?sort[name]=asc&sort[permission]=desc
User::query()->filter([
    Sort::make(), //  order by `name` asc, `permission` desc
]);

Detector

Sometimes, you want to setup your request with a prefix like filter.. You can config every pipe that have it

use Baro\PipelineQueryCollection\ExactFilter;

// users?filter[id]=4&filter[permission][0]=1&filter[permission][1]=4
User::query()->filter([
    ExactFilter::make('id')->detectBy('filter.'), // where id = 4
    BitwiseFilter::make('permission')->detectBy('filter.'), // where permission & 5 = 5
]);

Or, you can define it globally

// users?filter[id]=4&filter[permission][0]=1&filter[permission][1]=4

// .env
PIPELINE_QUERY_COLLECTION_DETECT_KEY="filter."

// Query
User::query()->filter([
    ExactFilter::make('id'), // where id = 4
    BitwiseFilter::make('permission'), // where permission & 5 = 5
]);

Custom search column

Sometimes, your request field is not the same with column name. For example, in your database you have column respond and want to perform some query against it, but for some reasons, your request query is reply instead of respond.

// users?reply=baro

User::query()->filter([
    RelativeFilter::make('reply')->filterOn('respond'), // where respond like '%baro%'
]);

Custom search value

Your value that need to be searched isn't from your request? No problems. You can use value() function to hard set the search value!

User::query()->filter([
    RelativeFilter::make('name')->value('Baro'), // where('name', 'like', "%Baro%")
]);

Extend filter

Yeah, you are free to use your own pipe. Take a look at some of my filters. All of them extends BaseFilter to have some useful properties and functions.

Testing

composer test

Contributing

Please see CONTRIBUTING for details.

Security Vulnerabilities

Please review our security policy on how to report security vulnerabilities.

Credits

• l3aro
• All Contributors

License

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