Skip to content
πŸ” Database/Eloquent Query Builder filters for Laravel
PHP
Branch: 1.x
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
src
tests/Unit
.coveralls.yml
.gitattributes
.gitignore
.php_cs
.travis.yml
CHANGELOG-1.x.md
LICENSE
README.md
composer.json
phpunit.xml

README.md

Database/Eloquent Query Builder filters for Laravel

Build Status Latest Stable Version Total Downloads Latest Unstable Version License Coverage Status

Installation

To install through composer, simply put the following in your composer.json file:

{
    "require": {
        "laravie/query-filter": "^1.0"
    }
}

And then run composer install from the terminal.

Quick Installation

Above installation can also be simplify by using the following command:

composer require "laravie/query-filter=^1.0"

Usages

Order Queries

new Laravie\QueryFilter\Orderable(?string $column, string $direction = 'asc', array $config = []);

The class provides a simple interface to handle ORDER BY queries to Laravel Eloquent/Query Builder.

use App\User;
use Laravie\QueryFilter\Orderable;

$query = App\User::query();

$orderable = new Orderable(
    'name', 'desc'
);

return $orderable->apply($query)->get(); 
select * FROM `users` ORDER BY `name` DESC;

The code will validate the column name before trying to apply orderBy() to the query, this would prevent SQL injection especially when column is given by the user.

Search Queries

new Laravie\QueryFilter\Searchable(?string $keyword, array $columns = []);

The class provides a simple interface to LIKE queries to Laravel Eloquent/Query Builder.

use App\User;
use Laravie\QueryFilter\Searchable;

$query = App\User::query();

$searchable = new Searchable(
    'crynobone', ['name', 'email']
);

return $searchable->apply($query)->get(); 
select * from `users` 
where (
    (
        `name` like 'crynobone' 
        or `name` like 'crynobone%'
        or `name` like '%crynobone'
        or `name` like '%crynobone%'
    ) or (
        `email` like 'crynobone' 
        or `email` like 'crynobone%'
        or `email` like '%crynobone'
        or `email` like '%crynobone%'
    )
);

Search with wildcard

Set specific % or * wildcard to reduce the possible LIKEs variations.

use App\User;
use Laravie\QueryFilter\Searchable;

$query = App\User::query();

$searchable = new Searchable(
    'crynobone*gmail', ['name', 'email']
);

return $searchable->apply($query)->get(); 
select * from `users` 
where (
    (
        `name` like 'crynobone%gmail'
    ) or (
        `email` like 'crynobone%gmail'
    )
);

Search with JSON path

This would allow you to query JSON path using LIKE with case insensitive (JSON path in MySQL is case-sensitive by default).

use App\User;
use Laravie\QueryFilter\Searchable;

$query = App\User::query();

$searchable = new Searchable(
    'Malaysia', ['address->country']
);

return $searchable->apply($query)->get(); 
select * from `users` 
where (
    (
        lower(`address`->'$.country') like 'malaysia'
        or lower(`address`->'$.country') like 'malaysia%'
        or lower(`address`->'$.country') like '%malaysia'
        or lower(`address`->'$.country') like '%malaysia%'
    )
);

Search with Relations

This would make it easy to search results not only in the current model but also it's relations.

use App\User;
use Laravie\QueryFilter\Searchable;

$query = App\User::query();

$searchable = new Searchable(
    'Administrator', ['name', 'roles.name']
);

return $searchable->apply($query)->get(); 
select * from `users` 
where (
    (
        `name` like 'Administrator' 
        or `name` like 'Administrator%'
        or `name` like '%Administrator'
        or `name` like '%Administrator%'
    ) or exists (
        select * from `roles` 
        inner join `user_role` 
            on `roles`.`id` = `user_role`.`role_id` 
        where `users`.`id` = `user_role`.`user_id` 
        and (
            `name` like 'Administrator' 
            or `name` like 'Administrator%' 
            or `name` like '%Administrator' 
            or `name` like '%Administrator%'
        )
    )
);

Relations search can only be applied to Illuminate\Database\Eloquent\Builder as it need to ensure that the relationship exists via whereHas() queries.

Taxonomy Queries

new Laravie\QueryFilter\Taxonomy(?string $keyword, array $rules, array $columns = []);

Taxonomy always developers to create a set of rules to group the search keywords using WHERE ... AND. For any un-grouped keyword it will be executed via Laravie\QueryFilter\Searchable based on given $columns.

use App\User;
use Laravie\QueryFilter\Taxonomy;

$query = App\User::query();

$taxonomy = new Taxonomy(
    'is:admin email:crynobone@gmail.com', [
        'email:*' => static function ($query, $value) {
            return $query->where('email', '=', $value);
        },
        'role:[]' => static function ($query, array $value) {
            return $query->whereIn('role', $value);
        },
        'is:admin' => static function ($query) {
            return $query->where('admin', '=', 1);
        },
    ],
);

$taxonomy->apply($query)->get();
select * from `user` 
where `email`='crynobone@gmail.com'
and `admin`=1;

Integrations

Using with Laravel Nova

You can override the default Laravel global and local search feature by adding the following methods on app/Nova/Resource.php:

<?php
namespace App\Nova;

use Laravel\Nova\Http\Requests\NovaRequest;
use Laravel\Nova\Resource as NovaResource;
use Laravie\QueryFilter\Searchable;

abstract class Resource extends NovaResource
{
    // ...
    
    /**
     * Apply the search query to the query.
     *
     * @param \Illuminate\Database\Eloquent\Builder $query
     * @param string                                $search
     *
     * @return \Illuminate\Database\Eloquent\Builder
     */
    protected static function applySearch($query, $search)
    {
        return $query->where(function ($query) use ($search) {
            static::applyResourceSearch($query, $search);
        });
    }

    /**
     * Apply the search query to the query.
     *
     * @param \Illuminate\Database\Eloquent\Builder $query
     * @param string                                $search
     *
     * @return \Illuminate\Database\Eloquent\Builder
     */
    protected static function applyResourceSearch($query, $search)
    {
        return $query->where(function ($query) use ($search) {
            (new Searchable(
                $search, static::searchableColumns()
            ))->apply($query);
        });
    } 
}
You can’t perform that action at this time.