Laravel Query Builder

Requirements

• PHP 7.3+
• Composer
• Laravel 8.0+

Require Laravel Query Builder using Composer:

composer require zing/laravel-query-builder

• Basic usage
• Search
  • Composite search
• Filter
  • Typed filter
  • Flagged filter
  • Cast Input(Skip auto cast)
• Sort
• Paginator

Usage

Basic usage

use Zing\QueryBuilder\QueryBuilder;
use Zing\QueryBuilder\Tests\Models\User;

// uri: /api/users?search=Harry&status=1,2,3&desc=created_at&per_page=10
// sql: select * from "users" where ("name" like "%Harry%") and "status" in ("1", "2", "3") order by "created_at" desc limit 11 offset 0
QueryBuilder::fromBuilder(User::class, $request)
    ->searchable(['name'])
    ->enableFilters(['is_visible', 'status'])
    ->enableSorts(['created_at'])
    ->enablePaginator()
    ->simplePaginate();

Search

use Zing\QueryBuilder\QueryBuilder;
use Zing\QueryBuilder\Tests\Models\User;

// uri: /api/users?search=Harry
// sql: select * from "users" where ("name" like "%Harry%" or "email" like "%Harry%") limit 16 offset 0
QueryBuilder::fromBuilder(User::class, $request)
    ->searchable(['name', 'email'])
    ->simplePaginate();

Composite search

⚠️ The filter with default value is not supported yet.

use Zing\QueryBuilder\Filter;
use Zing\QueryBuilder\QueryBuilder;
use Zing\QueryBuilder\Tests\Models\User;

// uri: /api/users?search=2021
// sql: select * from "users" where ("number" like "%2021%" or ("id" = "2021")) limit 16 offset 0
QueryBuilder::fromBuilder(User::class, $request)
    ->searchable(['number', Filter::exact('encoded_id', 'id')])
    ->simplePaginate();

Filter

use Zing\QueryBuilder\Filter;
use Zing\QueryBuilder\QueryBuilder;
use Zing\QueryBuilder\Tests\Models\User;

// uri: /api/users?name=Harry
// sql: select * from "users" where "name" like "%Harry%" limit 16 offset 0
QueryBuilder::fromBuilder(User::class, $request)
    ->enableFilters([Filter::partial('name')])
    ->simplePaginate();

use Zing\QueryBuilder\Filter;
use Zing\QueryBuilder\QueryBuilder;
use Zing\QueryBuilder\Tests\Models\User;

// uri: /api/users?status=1,2,3
// sql: select * from "users" where "status" in ("1", "2", "3") limit 16 offset 0
QueryBuilder::fromBuilder(User::class, $request)
    ->enableFilters([Filter::exact('status')])
    ->simplePaginate();

use Zing\QueryBuilder\Filter;
use Zing\QueryBuilder\QueryBuilder;
use Zing\QueryBuilder\Tests\Models\User;

// uri: /api/users?visible=1
// sql: select * from "users" where "is_visible" = true limit 16 offset 0
// uri: /api/users
// sql: select * from "users" where "is_visible" = true limit 16 offset 0
QueryBuilder::fromBuilder(User::class, $request)
    ->enableFilters([Filter::scope('visible')->default(true)])
    ->simplePaginate();

Typed filter

⚠️ The filter with default value is not supported yet.

use Zing\QueryBuilder\Filter;
use Zing\QueryBuilder\QueryBuilder;
use Zing\QueryBuilder\Tests\Models\Order;

// uri: /api/users?search_type=number&search_value=2021
// sql: select * from "orders" where "number" like "%2021%" limit 16 offset 0
QueryBuilder::fromBuilder(Order::class, $request)
    ->enableTypedFilter(
        'search_type',
        'search_value',
        [Filter::partial('number'), Filter::partial('user_name', 'user.name')]
    )
    ->simplePaginate();

Flagged filter

use Zing\QueryBuilder\Filter;
use Zing\QueryBuilder\QueryBuilder;
use Zing\QueryBuilder\Tests\Models\Order;

// uri: /api/users?number=2021&user_name=Jone
// sql: select * from "orders" where (("number" like "%2021%") or (exists (select * from "users" where "orders"."user_id" = "users"."id" and "users"."name" like "%Jone%"))) limit 16 offset 0
QueryBuilder::fromBuilder(Order::class, $request)
    ->enableFlaggedFilter([Filter::partial('number'), Filter::partial('user_name', 'user.name')])
    ->simplePaginate();

Cast Input(Skip auto cast)

use Zing\QueryBuilder\Enums\CastType;
use Zing\QueryBuilder\Filter;
use Zing\QueryBuilder\QueryBuilder;
use Zing\QueryBuilder\Tests\Models\User;

// uri: /api/users?is_visible=true
// sql: select * from "users" where "is_visible" = true limit 16 offset 0
// uri: /api/users?is_visible=true,false
// sql: select * from "users" where "is_visible" in (true, false) limit 16 offset 0
QueryBuilder::fromBuilder(User::class, $request)
    ->enableFilters(Filter::exact('is_visible')->withCast(CastType::BOOLEAN))
    ->simplePaginate();

use Zing\QueryBuilder\Enums\CastType;
use Zing\QueryBuilder\Filter;
use Zing\QueryBuilder\QueryBuilder;
use Zing\QueryBuilder\Tests\Models\Order;

// uri: /api/orders?content=code,and
// sql: select * from "orders" where "content" like "%code,and%" limit 16 offset 0
QueryBuilder::fromBuilder(Order::class, $request)
    ->enableFilters(Filter::partial('content')->withCast(CastType::ORIGINAL))
    ->simplePaginate();

Sort

use Zing\QueryBuilder\QueryBuilder;
use Zing\QueryBuilder\Sort;
use Zing\QueryBuilder\Tests\Models\Order;

// uri: /api/users?desc=created_date
// sql: select * from "orders" order by "created_at" desc limit 16 offset 0
QueryBuilder::fromBuilder(Order::class, $request)
    ->enableSorts([Sort::field('created_date', 'created_at')])
    ->simplePaginate();

Paginator

use Zing\QueryBuilder\QueryBuilder;
use Zing\QueryBuilder\Tests\Models\User;

// uri: /api/users?size=5
// sql: select * from "users" limit 6 offset 0
QueryBuilder::fromBuilder(User::class, $request)
    ->enablePaginator('size')
    ->simplePaginate();

use Zing\QueryBuilder\Paginator;
use Zing\QueryBuilder\QueryBuilder;
use Zing\QueryBuilder\Tests\Models\User;

// uri: /api/users?size=
// sql: select * from "users" limit 6 offset 0
QueryBuilder::fromBuilder(User::class, $request)
    ->enablePaginator(Paginator::name('size')->default(5))
    ->simplePaginate();

License

Laravel Query Builder is an open-sourced software licensed under the MIT license.