v3.14
3.14 3.13 3.12 2.14 1.0
Version: 3.14
3.14 3.13 3.12 2.14 1.0
Revision: Fri, 20 Dec 2024 23:34:21 GMT
Edit this page

Component — Data Grids

Leveraging the power of Spiral, the spiral/data-grid component provides developers with a streamlined solution to generate Cycle and DBAL select queries automatically, based on user specifications.

Warning:
Spiral does not provide Cycle ORM out of the box. To use this component, you need to install spiral/cycle-bridge component. You can find more information about Cycle ORM Bridge in the The Basics — Database and ORM section

When should you consider the Data Grid Component?

  1. RESTful API Development: Imagine having the power to construct dynamic APIs that can filter, sort, and manage relational data based on user requests, all without writing tedious and repetitive query code.

  2. Data Visualization Platforms: If your application needs to render vast datasets in tabular formats, with capabilities like sorting by columns, filtering records, and paginating results, the Data Grid Component is your ticket to achieving this with minimal effort.

  3. Content Management Systems (CMS): For platforms that frequently fetch and display data based on user roles, preferences, or search criteria, this component can drive efficiency, making data retrieval swift and straightforward.

  4. E-Commerce Platforms: Think of scenarios where users filter products by categories, sort by price, or paginate through thousands of items. The Data Grid Component can make these operations fluid and user-friendly.

By leveraging the Data Grid Component, developers can achieve a clear boundary between application layers:

  • Automated Query Handling: Instead of cluttering your interface layer with detailed query instructions, define user-driven specifications (like filtering or sorting) and let the component automatically construct the necessary domain-specific queries.

  • Unified Data Representation: Once the domain layer fetches or processes the data, the component can standardize how this data is presented, regardless of its source or complexity.

  • Decoupled Input Sources: The component's versatility in handling multiple input sources ensures that the domain layer remains agnostic to the origin of requests, whether it's HTTP, console, or gRPC.

  • Extendable Grid Schemas: Design grid schemas in line with your domain structures and let the interface layer adapt them dynamically based on user input, maintaining a distinct boundary between data structure and presentation logic.

Installation

To install the component:

composer require spiral/data-grid-bridge spiral/cycle-bridge

Activate the bootloader Spiral\DataGrid\Bootloader\GridBootloader in your application after Cycle bootloaders:

php
app/src/Application/Kernel.php
public function defineBootloaders(): array
{
    return [
        // ...
        \Spiral\DataGrid\Bootloader\GridBootloader::class,
        // ...
    ];
}

Read more about bootloaders in the Framework — Bootloaders section.

Quick Start

After installation, set up the writers for your data sources in the app/config/dataGrid.php config.

Here's a basic setup for Cycle ORM Bridge:

php
app/config/dataGrid.php
return [
    'writers' => [
        \Spiral\Cycle\DataGrid\Writer\QueryWriter::class,
        \Spiral\Cycle\DataGrid\Writer\PostgresQueryWriter::class,
        \Spiral\Cycle\DataGrid\Writer\BetweenWriter::class,
    ],
];

Note
As you can see, we can register multiple writers for different specifications.

Usage

The component provides two base abstractions - Grid Factory and Grid Schema.

Grid Schema

Think of the Schema as a set of rules for how to get data based on what the user asks for.

Here is a simple example of a grid schema:

php
use Spiral\DataGrid\GridSchema; 
use Spiral\DataGrid\Specification\Filter\Like;
use Spiral\DataGrid\Specification\Pagination\PagePaginator;
use Spiral\DataGrid\Specification\Sorter\Sorter;
use Spiral\DataGrid\Specification\Value\StringValue;

$schema = new GridSchema();

// User pagination: limit results to 10 per page
$schema->setPaginator(new PagePaginator(10));

// Sorting option: by id
$schema->addSorter('id', new Sorter('id'));

// Filter option: find by name matching user input
$schema->addFilter('name', new Like('name', new StringValue()));

In short, with Schema, developers can customize filters and sorting options when fetching data.

For a cleaner setup, you can extend the GridSchema and set everything inside its constructor, like in the example below:

php
use Spiral\DataGrid\GridSchema; 
use Spiral\DataGrid\Specification\Pagination\PagePaginator;
use Spiral\DataGrid\Specification\Sorter\Sorter;
use Spiral\DataGrid\Specification\Filter\Like;
use Spiral\DataGrid\Specification\Value\StringValue;

class UserSchema extends GridSchema
{
    public function __construct()
    {
        // User pagination: limit results to 10 per page
        $this->setPaginator(new PagePaginator(10));
        
        // Sorting option: by id
        $this->addSorter('id', new Sorter('id'));
        
        // Filter option: find by name matching user input
        $this->addFilter('name', new Like('name', new StringValue()));
    }
}

Grid Factory

Grid Factory is the link between your grid schema and the actual data you want to retrieve. The following code example demonstrates how to connect the schema to data using the Cycle ORM Repository:

php
use Spiral\DataGrid\GridSchema;
use Spiral\DataGrid\GridFactoryInterface;

$schema = new UserSchema();

$factory = $container->get(GridFactoryInterface::class);
$users = $container->get(\App\Database\UserRepository::class);
  
/** @var Spiral\DataGrid\GridInterface $result */
$result = $factory->create($users->select(), $schema);  

// Fetch the refined data
print_r(iterator_to_array($result));

You can also set default specifications:

php
/** @var Spiral\DataGrid\GridFactory $factory */
$factory = $factory->withDefaults([
    GridFactory::KEY_SORT     => ['id' => 'desc'],
    GridFactory::KEY_FILTER   => ['name' => 'Antony'],
    GridFactory::KEY_PAGINATE => ['page' => 3, 'limit' => 100]
]);

Note
Because the withDefaults method is immutable, calling it doesn't alter the original Grid Factory. Instead, it gives you a new instance with the specified defaults.

How to apply the specifications:

  • to select users from the second page open page with POST or QUERY data like: ?paginate[page]=2
  • to activate the like filter: ?filter[name]=antony
  • to sort by id in ASC or DESC: ?sort[id]=desc
  • to get count of total values: ?fetchCount=1

Finally, the last code example shows what a sample controller might look like when putting everything together:

php
app/src/Endpoint/Web/Controller/UserController.php
use Spiral\DataGrid\GridInterface;
use Spiral\DataGrid\GridFactoryInterface;
use App\Database\UserRepository;

class UserController
{
    #[Route('/users')]
    public function index(UserSchema $schema, GridFactoryInterface $factory, UserRepository $users): array
    {
        /** @var GridInterface $result */
        $result = $factory->create($users->select(), $schema);
        
        $values = [];

        foreach ([
            GridInterface::FILTERS, 
            GridInterface::SORTERS, 
            GridInterface::COUNT, 
            GridInterface::PAGINATOR
        ] as $key) {
             $values[$key] = $result->getOption($key);
        }
        
        return [
            'users' => iterator_to_array($result),
            'grid' => [
                'values' => $values,
            ],
        ];
    }
}

Input Sources

The Grid Factory gets its data from Spiral\DataGrid\InputInterface. By default, it takes this data from an HTTP request through Spiral\Http\Request\InputManager.

However, what's great about the component is its flexibility. It doesn't only work with HTTP requests. You can make it work with things like command prompts, gRPC requests, or other sources. Just use the InputInterface for your chosen data source and set up the DataGrids for your app's needs.

There are two main methods to switch out where the data comes from:

  1. Local Changes with GridFactory::withInput: Ideal for temporary changes like during testing. Here's an example using an array as an input:
php
use Spiral\DataGrid\Input\ArrayInput;

/** @var Spiral\DataGrid\GridFactory $factory */
$factory = $factory->withInput(new ArrayInput([
    'name' => 'antony',
    'id' => 'desc'
]));

/** @var Spiral\DataGrid\GridInterface $result */
$result = $factory->create($users->select(), $schema);

Note
Because the withInput method is immutable, calling it doesn't alter the original Grid Factory. Instead, it gives you a new instance with the specified input.

  1. Setting a Global Input Source via the Container: Here, you're changing the input source for the entire application.

To illustrate, this is how you'd modify the Grid Factory to obtain input from the console:

php
app/src/Application/Bootloader/AppBootloader.php
use Spiral\Boot\Bootloader\Bootloader;

class AppBootloader extends Bootloader
{
    protected const SINGLETONS = [
        \Spiral\DataGrid\InputInterface::class => ConsoleInput::class,
    ];
}

Grid writers

In Data Grid, we have Schemas to outline how data should be managed and Factories to define where this data comes from. On top of these, we also have Writers.

Their job? To change the data based on user inputs.

Think of them like this:

If you have data in a book and you use a pencil (the writer) to add, modify, or erase content, then that pencil is the grid writer. Spiral has writers for Cycle ORM. But the cool part is that you can make your own pencil for other systems like Doctrine Collections.

How to Create Your Own Writer

Want to make your own pencil (or writer)? Follow these steps:

  1. Use the Spiral\DataGrid\WriterInterface as your guide.

Here's an example:

php
app/src/Application/Schema/DoctrineCollectionWriter.php
<?php

declare(strict_types=1);

namespace App\Application\Schema;

use Doctrine\Common\Collections\Collection;
use Doctrine\Common\Collections\Criteria;
use Doctrine\Common\Collections\Expr\Comparison;
use Spiral\DataGrid\Compiler;
use Spiral\DataGrid\Specification\Filter\Equals;
use Spiral\DataGrid\Specification\Filter\Like;
use Spiral\DataGrid\Specification\Sorter\AbstractSorter;
use Spiral\DataGrid\SpecificationInterface;
use Spiral\DataGrid\WriterInterface;

final class DoctrineCollectionWriter implements WriterInterface
{
    public function write(mixed $source, SpecificationInterface $specification, Compiler $compiler): mixed
    {
        // If the data isn't in a collection, just return it as is.
        if (!$source instanceof Collection) {
            return $source;
        }

        // Prepare a set of rules to change the data.
        $criteria = null;

        // If the change is about sorting.
        if ($specification instanceof AbstractSorter) {
            $orders = [];
            foreach ($specification->getExpressions() as $field) {
                $orders[$field] = ($specification->getValue() === AbstractSorter::ASC)
                    ? Criteria::ASC
                    : Criteria::DESC;
            }

            if ($orders !== []) {
                $criteria = (new Criteria())->orderBy($orders);
            }
        // If the change is about matching exact values.
        } elseif ($specification instanceof Equals) {
            $expr = new Comparison($specification->getExpression(), Comparison::EQ, $specification->getValue());
            $criteria = (new Criteria())->where($expr);
        // If the change is about checking if data contains certain text.
        } elseif ($specification instanceof Like) {
            $criteria = new Criteria(
                Criteria::expr()->contains($specification->getExpression(), $specification->getValue())
            );
        } else {
            // ... and so on.
            return null;
        }

        // Apply the changes if there are any.
        if ($criteria !== null) {
            $source = $source->matching($criteria)->getValues();
        }

        return $source;
    }
}

If a writer returns null, the compiler will think that the writer doesn't know how to handle the specification and in case if all the writers return null, the compiler will throw an exception Spiral\DataGrid\Exception\CompilerException. Essentially, it's the system's way of saying, "Hey, something isn't right here. None of the writers did their job."

Why It's Important

Imagine you're trying to update a record in a database. You've given the system a set of rules on how to make this update. You'd expect one of two outcomes: either the record is successfully updated or there's a problem with the update parameters.

The CompilerException serves as a feedback mechanism. Instead of silently failing and leaving developers scratching their heads, Spiral explicitly alerts them that none of the writers made any changes. This feedback can be invaluable for debugging and ensuring data integrity.

  1. Register your writer in the app/config/dataGrid.php config.

We need to register the writer in the writers section. The order of writers is important, as the compiler will use them in the same order as they are registered.

php
app/config/dataGrid.php
return [
    'writers' => [
        \App\Application\Schema\DoctrineCollectionWriter::class,
    ],
];

That's it! Now you can try to pass a Doctrine Collection to the grid factory and see how it works.

Counting items

If you need to count items using a complex function, you can pass a callable function via withCounter method:

php
/** @var Spiral\DataGrid\GridFactory $factory */
$factory = $factory->withCounter(static function ($select): int {
    return count($select) * 2;
});

Note
This is a simple example, but this function might be very helpful in case of complex SQL requests with joins.

Pagination specifications

Page Paginator specification

This is a simple page+limit pagination:

php
use Spiral\DataGrid\GridSchema;
use Spiral\DataGrid\Specification\Pagination\PagePaginator;

$schema = new GridSchema();
$schema->setPaginator(new PagePaginator(10, [25, 50, 100, 500]));
// ...

From the user input, such paginator accepts an array with 2 keys, limit and page. If the limit is set, it should be presented in the allowedLimits constructor param.

php
use Spiral\DataGrid\Specification\Pagination\PagePagination;

$paginator = new PagePaginator(10, [25, 50, 100, 500]);

$paginator->withValue(['limit' => 123]); // won't apply
$paginator->withValue(['limit' => 50]);  // will apply
$paginator->withValue(['limit' => 100]); // will apply

$paginator->withValue(['limit' => 100, 'page' => 2]);

Under the hood, this paginator converts limit and page into the Limit and Offset specification. You are free to write your own paginator, such as a cursor-based one (for example: lastID+limit).

Sorter specifications

Sorters are specifications that carry a sorting direction. For sorters that can apply direction, you can pass one of the next values:

  • 1, '1', 'asc', SORT_ASC for ascending order
  • -1, '-1', 'desc', SORT_DESC for descending order

Next specifications are available for grids for now:

Ordered sorters

AscSorter and DescSorter contain expressions that should be applied with ascending (or descending) sorting order:

php
use Spiral\DataGrid\Specification\Sorter;

$ascSorter = new Sorter\AscSorter('first_name', 'last_name');
$descSorter = new Sorter\DescSorter('first_name', 'last_name');

Directional sorter

This sorter contains 2 independent sorters, each for ascending and descending order. By receiving the order via withValue, we will get one of the sorters:

php
use Spiral\DataGrid\Specification\Sorter;

$sorter = new Sorter\DirectionalSorter(
    new Sorter\AscSorter('first_name'),
    new Sorter\DescSorter('last_name')
);

// will sort by first_name asc
$ascSorter = $sorter->withDirection('asc');

// will sort by last_name desc
$descSorter = $sorter->withDirection('desc');

Note
that you can sort using a different set of fields in both sorters. If you have the same set of fields, use a sorter instead.

Sorter

This is a sorter wrapper for a directional sorter in case you have the same fields for sorting in both directions:

php
use Spiral\DataGrid\Specification\Sorter;

$sorter = new Sorter\Sorter('first_name', 'last_name');

// will sort by first_name and last_name asc
$ascSorter = $sorter->withDirection('asc');

// will sort by first_name and last_name desc
$descSorter = $sorter->withDirection('desc');

Sorter set

This is just a way of combining sorters into one set, passing direction will apply it to the whole set:

php
use Spiral\DataGrid\Specification\Sorter;

$sorter = new Sorter\SorterSet(
    new Sorter\AscSorter('first_name'),
    new Sorter\DescSorter('last_name'),
    new Sorter\Sorter('email', 'username')
    // ...
);

// will sort by first_name, email and username asc, also last_name desc
$ascSorter = $sorter->withDirection('asc');

// will sort by last_name, email and username desc, also first_name asc
$descSorter = $sorter->withDirection('desc');

Filter specifications

Filters are specifications that carry values. Values can be passed directly via the constructor. In this case, the filter value is fixed and will be applied as it is.

php
use Spiral\DataGrid\Specification\Filter;

// name should be 'Antony'
$filter = new Filter\Equals('name', 'Antony');

// name is still 'Antony' 
$filter = $filter->withValue('John');

If you pass the ValueInterface to the constructor, you can use withValue() method. Then, it will be checked if the incoming value matches the ValueInterface type and will be converted.

php
use Spiral\DataGrid\Specification\Filter;
use Spiral\DataGrid\Specification\Value;

// price is not defined yet
$filter = new Filter\Equals('price', new Value\NumericValue());

// the value will be converted to int and the price should be equal to 7  
$filter = $filter->withValue('7'); 

// this value is not applicable as it is not numeric  
$filter = $filter->withValue([123]);

The next specifications are available for grids now:

Note
There are more interesting things in the filter values and value accessors sections below.

All

This is a union filter for logic and operation.

Some examples with fixed values:

php
use Spiral\DataGrid\Specification\Filter;

// the price should be equal to 2 and the quantity should be greater than 5
$all = new Filter\All(
    new Filter\Equals('price', 2),
    new Filter\Gt('quantity', 5)
);

Passed value will be applied to all sub-filters:

Examples with ValueInterface usage:

php
use Spiral\DataGrid\Specification\Filter;
use Spiral\DataGrid\Specification\Value;

$all = new Filter\All(
    new Filter\Equals('price', new Value\NumericValue()),
    new Filter\Gt('quantity', new Value\IntValue()),
    new Filter\Lt('option_id', 4)
);

// the price should be equal to 5, the quantity should be greater than 5 and the option_id less than 4
$all = $all->withValue(5);

Any

This is a union filter for logic or operation.

Examples with fixed values:

php
use Spiral\DataGrid\Specification\Filter;

// the price should be equal to 2 or the quantity be greater than 5
$any = new Filter\Any(
    new Filter\Equals('price', 2),
    new Filter\Gt('quantity', 5)
);

Passed value will be applied to all sub-filters.

Examples with ValueInterface usage:

php
use Spiral\DataGrid\Specification\Filter;
use Spiral\DataGrid\Specification\Value;

$any = new Filter\Any(
    new Filter\Equals('price', new Value\NumericValue()),
    new Filter\Gt('quantity', new Value\IntValue()),
    new Filter\Lt('option_id', 4)
);

// the price should be equal to 5 or the quantity should be greater than 5 or the option_id less than 4
$any = $any->withValue(5);

(Not) equals

These are simple expression filters for logic =, != operations.

Examples with a fixed value:

php
use Spiral\DataGrid\Specification\Filter;

$equals = new Filter\Equals('price', 2);       // the price should be equal to 2
$notEquals = new Filter\NotEquals('price', 2); // the price should not be equal to 2

Examples with ValueInterface usage:

php
use Spiral\DataGrid\Specification\Filter;
use Spiral\DataGrid\Specification\Value;

// the price should be equal to 2
$equals = new Filter\Equals('price', new Value\NumericValue());
$equals = $equals->withValue('2');

// the price should not be equal to 2
$notEquals = new Filter\NotEquals('price', new Value\NumericValue());
$notEquals = $notEquals->withValue('2');

Compare

These are simple expression filters for logic >, >=, <, <= operations.

Examples with a fixed value:

php
use Spiral\DataGrid\Specification\Filter;

$gt = new Filter\Gt('price', 2);   // the price should be greater than 2
$gte = new Filter\Gte('price', 2); // the price should be greater than 2 or equal
$lt = new Filter\Lt('price', 2);   // the price should be less than 2
$lte = new Filter\Lte('price', 2); // the price should be less than 2 or equal

Examples with ValueInterface usage:

php
use Spiral\DataGrid\Specification\Filter;
use Spiral\DataGrid\Specification\Value;

// the price should be greater than 2
$gt = new Filter\Gt('price', new Value\NumericValue());
$gt = $gt->withValue('2');

// the price should be greater than 2 or equal
$gte = new Filter\Gte('price', new Value\NumericValue());
$gte = $gte->withValue('2');

// the price should be less than 2
$lt = new Filter\Lt('price', new Value\NumericValue());
$lt = $lt->withValue('2');

// the price should be less than 2 or equal
$lte = new Filter\Lte('price', new Value\NumericValue());
$lte = $lte->withValue('2');

(Not) in array

These are simple expression filters for logic in, not in operations.

Examples with a fixed value:

php
use Spiral\DataGrid\Specification\Filter;

$inArray = new Filter\InArray('price', [2, 5]);       // the price should be in array of 2 and 5
$notInArray = new Filter\NotInArray('price', [2, 5]); // the price should not be in array of 2 and 5

Examples with ValueInterface usage:

php
use Spiral\DataGrid\Specification\Filter;
use Spiral\DataGrid\Specification\Value;

// the price should be in array of 2 and 5
$inArray = new Filter\InArray('price', new Value\NumericValue());
$inArray = $inArray->withValue(['2', '5']);

// the price should not be in array of 2 and 5
$notInArray = new Filter\NotInArray('price', new Value\NumericValue());
$notInArray = $notInArray->withValue(['2', '5']);

Third param allows auto-wrapping the ValueInterface with ArrayValue (enabled by default). In case you have a non-trivial value (or wrapped with an accessor value), pass false as 3rd argument to control the filter wrapping:

php
use Spiral\DataGrid\Specification\Filter;
use Spiral\DataGrid\Specification\Value;
use Spiral\DataGrid\Specification\Value\Accessor\Split;
use Spiral\DataGrid\SpecificationInterface;

$inArray = new Filter\InArray('field', new Split(new Value\ArrayValue(new Value\IntValue()), '|'), false);
$inArray->withValue('1|2|3')->getValue(); // [1, 2, 3]

Like

This is a simple expression filter for like operation.

Examples with a fixed value:

php
use Spiral\DataGrid\Specification\Filter;

$likeFull = new Filter\Like('name', 'Tony', '%%%s%%'); // the name should be like '%Tony%'
$likeEnding = new Filter\Like('name', 'Tony', '%s%%'); // the name should be like 'Tony%'

Examples with ValueInterface usage:

php
use Spiral\DataGrid\Specification\Filter;
use Spiral\DataGrid\Specification\Value;

// the name should be like '%Tony%'
$like = new Filter\Like('name', new Value\StringValue());
$like = $like->withValue('Tony');

Map

Map is a complex filter representing a map of filters with their own values.

php
use Spiral\DataGrid\Specification\Filter;

// the price should be greater than 2 and the quantity be less than 5
$map = new Filter\Map([
    'from' => new Filter\Gt('price', 2),
    'to'   => new Filter\Lt('quantity', 5)
]);

Passed values will be applied to all sub-filters, all values are required:

Examples with ValueInterface usage:

php
use Spiral\DataGrid\Specification\Filter;
use Spiral\DataGrid\Specification\Value;

$map = new Filter\Map([
    'from' => new Filter\Gt('price', new Value\NumericValue()),
    'to'   => new Filter\Lt('quantity', new Value\NumericValue())
]);

// the price should be greater than 2 and the quantity should be less than 5
$map = $map->withValue(['from' => 2, 'to' => 5]);

// invalid input, map will be set to null
$map = $map->withValue(['to' => 5]);

Select

This specification represents a set of available expressions. Passing a value from the input will pick a single or several specifications from this set.

Note
You just need to pass a key or an array of keys. Note that no ValueInterface should be declared.

Example with a single value:

php
use Spiral\DataGrid\Specification\Filter;

// note, that we have integer keys here
$select = new Filter\Select([
    new Filter\Equals('name', 'value'),
    new Filter\Any(
        new Filter\Equals('price', 2),
        new Filter\Gt('quantity', 5)
    ),
    new Filter\Equals('email', 'email@example.com'),
]);

// select the second filter, will be equal to 'any' specification.
$filter = $select->withValue(1);

Example with multiple values:

php
use Spiral\DataGrid\Specification\Filter;

$select = new Filter\Select([
    'one'  => new Filter\Equals('name', 'value'),
    'two'  => new Filter\Any(
        new Filter\Equals('price', 2),
        new Filter\Gt('quantity', 5)
    ),
    'three' => new Filter\Equals('email', 'email@example.com'),
]);

// the filter will contain both sub-filters wrapped in 'all' specification
$filter = $select->withValue(['one', 'two']);

Example with an unknown value:

php
use Spiral\DataGrid\Specification\Filter;

$select = new Filter\Select([
    'one'  => new Filter\Equals('name', 'value'),
    'two'  => new Filter\Any(
        new Filter\Equals('price', 2),
        new Filter\Gt('quantity', 5)
    ),
    'three' => new Filter\Equals('email', 'email@example.com'),
]);

// filter will be equal to null
$filter = $select->withValue('four');

Between

This filter represents the SQL between operation, but can be presented as two gt/gte and lt/lte filters.

You have an ability to define whether the boundary values should be included or not. If the boundary values aren't included, this filter will be converted into gt+lt filters, otherwise when getting filters via getFilters() method, you can specify either usage of the original between operator or gte+lte filters.

Note
Not all databases support between operation, that's why the conversion to gt/gte+lt/lte is used by default.

Between filter has two modifications: field-based and value-based.

php
use Spiral\DataGrid\Specification\Filter;

$fieldBetween  = new Filter\Between('field', [10, 20]);
$valueBetween  = new Filter\ValueBetween('2020 Apr, 10th', ['start_date', 'end_date']);

Examples above are similar to the next SQL queries:

sql
#
field-based
select *
from table_name
where field between 10 and 20;
#
or using gte/lte conversion
select *
from table_name
where field >= 10
  and field <= 20;

#
value-based
select *
from table_name
where '2020 Apr, 10th' between start_date and end_date;
#
or using gte/lte conversion
select *
from table_name
where start_date <= '2020 Apr, 10th'
  and end_date >= '2020 Apr, 10th';

Example using ValueInterface:

php
use Spiral\DataGrid\Specification\Filter;
use Spiral\DataGrid\Specification\Value;

// the price should be between 10 and 20
$fieldBetween  = new Filter\Between('price', new Value\NumericValue());
$fieldBetween = $fieldBetween->withValue([10, '20']);

// the '2020 Apr, 10th' should be between start_date and end_date
$valueBetween  = new Filter\ValueBetween(new Value\DatetimeValue(), ['start_date', 'end_date']);
$valueBetween = $valueBetween->withValue('2020 Apr, 10th');

Select a render type:

php
use Spiral\DataGrid\Specification\Filter;

$between  = new Filter\Between('price', [10, 20]);

$between->getFilters();     // will be converted to gte+lte
$between->getFilters(true); // will be presented as is

$notIncludingBetween  = new Filter\Between('price', [10, 20], false, false);

// will be converted to gte+lte anyway
$notIncludingBetween->getFilters();
$notIncludingBetween->getFilters(true);

Note
The same is for ValueBetween filter.

Mixed Specifications

Spiral\DataGrid\Specification\Filter\SortedFilter and Spiral\DataGrid\Specification\Sorter\FilteredSorter are special sequence specifications that allow using both filters and sorters under a single name.

Usage:

php

$schema->addFilter(
    'filter',
    new Filter\Select(
        [
            'upcoming'      => new Sorter\SortedFilter(
                'upcoming',
                new Filter\Gt('date', new DateTimeImmutable('now')),
                new Sorter\AscSorter('date')
            ),
            'mostReviewed'  => new Sorter\SortedFilter(
                'mostReviewed',
                new Filter\Lte('date', new DateTimeImmutable('now')),
                new Sorter\DescSorter('count_reviews')
            )
        ]
    )
);

Note
We apply both sorting and filtering using the upcoming filter.

Filter values

We use filter values to convert the input type and its validation. Please don't use convert() method without validating the input via accepts() method. They can tell you if the input is acceptable and converts it to a desired type. Next values are available for grids for now:

Any

This value accepts any input and doesn't convert them:

php
use Spiral\DataGrid\Specification\Value;

$value = new Value\AnyValue();
 
$value->accepts('123'); // always true
$value->convert('123'); // always equal to the input

Array

This value expects an array and converts all of them according to the base value type. The input should not be empty:

php
use Spiral\DataGrid\Specification\Value;

// expects an array of int values
$value = new Value\ArrayValue(new Value\IntValue());
 
$value->accepts('123');   // false
$value->accepts([]);      // false
$value->accepts(['123']); // true
$value->convert(['123']); // [123]

Bool

This value expects a bool input, 1/0 (as int or strings), and true/false strings:

php
use Spiral\DataGrid\Specification\Value;

$value = new Value\BoolValue();
 
$value->accepts('123');   // false
$value->accepts('0');     // true
$value->accepts(['123']); // false
$value->convert('1');     // true
$value->convert('false'); // false

Zero-compare

These values are supposed to check your input if it is positive/negative/non-positive/non-negative according to the base value type:

php
use Spiral\DataGrid\Specification\Value;

$positive = new Value\PositiveValue(new Value\IntValue());       // as int should be > 0
$negative = new Value\NegativeValue(new Value\IntValue());       // as int should be < 0
$nonPositive = new Value\NonPositiveValue(new Value\IntValue()); // as int should be >= 0
$nonNegative = new Value\NonNegativeValue(new Value\IntValue()); // as int should be <= 0

Numbers

Applies numeric values, also empty strings (zero is also a value):

php
use Spiral\DataGrid\Specification\Value;

$int = new Value\IntValue();         // converts to int
$float = new Value\FloatValue();     // converts to float
$numeric = new Value\NumericValue(); // converts to int/float

Datetime

This value expects a string representing a timestamp or a datetime and converts it into a \DateTimeImmutable:

php
use Spiral\DataGrid\Specification\Value;

$value = new Value\DatetimeValue();
 
$value->accepts('abc');     // false
$value->accepts('123');     // true
$value->accepts('-1 year'); // true
$value->convert('-1 year'); // DateTimeImmutable object

Datetime Format

This value expects a string representing a datetime formatted according to the given format. A datetime is converted into a \DateTimeImmutable, a datetime will be additionally formatted using the output format if the 2nd argument is passed:

php
use Spiral\DataGrid\Specification\Value;

$value = new Value\DatetimeFormatValue('Y-m-d');
 
$value->accepts('2020 Jan 21st');  // false
$value->accepts('2020-01-21');     // true

$value->convert('2020-01-21'); // DateTimeImmutable object

$value = new Value\DatetimeFormatValue('Y-m-d', 'F dS, y');
$value->convert('2020-01-21'); // January 21st, 20

Enum

This value expects an input to be a part of a given enum array and converts it according to the base value type. All enum values are also converted:

php
use Spiral\DataGrid\Specification\Value;

// expects an array of int values
$value = new Value\EnumValue(new Value\IntValue(), 1, '2', 3);
 
$value->accepts('3'); // true
$value->accepts(4);   // false
$value->convert('3'); // 3

Intersect

This value is based on an enum value, the difference is that at least one of the array input elements should match the given enum array:

php
use Spiral\DataGrid\Specification\Value;

// expects an array of int values
$value = new Value\IntersectValue(new Value\IntValue(), 1, '2', 3);
 
$value->accepts('3');    // true
$value->accepts(4);      // false
$value->accepts([3, 4]); // true
$value->convert('3');    // [3]

Subset

This value is based on an enum value, the difference is that all of the array input elements should match the given enum array:

php
use Spiral\DataGrid\Specification\Value;

// expects an array of int values
$value = new Value\SubsetValue(new Value\IntValue(), 1, '2', 3);
 
$value->accepts('3');    // true
$value->accepts(4);      // false
$value->accepts([3, 4]); // false
$value->accepts([2, 3]); // true
$value->convert('3');    // [3]

String

Applies string-like input, also empty strings (if a corresponding constructor param is passed):

php
use Spiral\DataGrid\Specification\Value;

$value = new Value\StringValue();
$allowEmpty = new Value\StringValue(true);

$value->accepts('');      // false
$value->accepts(false);   // false
$value->accepts('3');     // true
$value->accepts(4);       // true
$value->convert(3);       // '3'
$allowEmpty->accepts(''); // true

Scalar

Applies scalar values, also empty strings (if a corresponding constructor param is passed):

php
use Spiral\DataGrid\Specification\Value;

$value = new Value\ScalarValue();
$allowEmpty = new Value\ScalarValue(true);

$value->accepts('');       // false
$value->accepts(false);    // true
$value->accepts('3');      // true
$value->accepts(4);        // true
$value->convert(3);        // '3'
$allowEmpty->accepts('');  // true

Regex

Applies string-like input and checks if it matches the given regex pattern, converts to string:

php
use Spiral\DataGrid\Specification\Value;

$value = new Value\RegexValue('/\d+/');

$value->accepts('');  // false
$value->accepts(3);   // true
$value->accepts('4'); // true
$value->convert(3);   // '3'

Uuid

Applies UUID-formatted strings, a user can choose which validation pattern to use:

  • any (just check the string format)
  • nil (special uuid null value)
  • one of [1-5] versions

The output is converted to string.

php
use Spiral\DataGrid\Specification\Value;

$v4 = new Value\UuidValue('v4');
$valid = new Value\UuidValue();

$v4->accepts('');                                        // false
$v4->accepts('00000000-0000-0000-0000-000000000000');    // false
$valid->accepts('');                                     // false
$valid->accepts('00000000-0000-0000-0000-000000000000'); // true

Range

This value expects an input to be the inside of a given range and converts it according to the base value type. Range boundary values are also converted. You can also specify if the input can be equal to the boundary values or not:

php
use Spiral\DataGrid\Specification\Value;

// as it, expects the value be >=1 and <3
$value = new Value\RangeValue(
    new Value\IntValue(),
    Value\RangeValue\Boundary::including(1),
    Value\RangeValue\Boundary::excluding(3)
);
 
$value->accepts('3'); // false
$value->accepts(1);   // false

Not-Empty

If you need to check first that the value isn't empty, wrap the value with NotEmpty():

php
use Spiral\DataGrid\Specification\Value;

$int = new Value\IntValue();
$notEmpty = new Value\NotEmpty($int);

$int->accepts(0);      // true
$notEmpty->accepts(0); // false

Value accessors

Accessors act like values from the section above but have another purpose - you can use them to perform not-type transformations, for example, using strings, you may want to trim the value or convert it to uppercase. They can be applied only if the value is applicable by a given ValueInterface. Examples below:

php
use Spiral\DataGrid\Specification\Value;
use Spiral\DataGrid\Specification\Value\Accessor;

(new Accessor\ToUpper(new Value\StringValue()))->convert('abc'); // 'ABC'
(new Accessor\ToUpper(new Value\StringValue()))->convert('ABC'); // 'ABC'
(new Accessor\ToUpper(new Value\StringValue()))->convert(123);   // '123'
(new Accessor\ToUpper(new Value\ScalarValue()))->convert(123);   // 123

All supported accessors have the next handling order: perform own operations first, then pass them to a lower level. For example, we have add and multiply accessors:

php
use Spiral\DataGrid\Specification\Value;
use Spiral\DataGrid\Specification\Value\Accessor;

$multiply = new Accessor\Multiply(new Accessor\Add(new Value\IntValue(), 2), 2);
$add = new Accessor\Add(new Accessor\Multiply(new Value\IntValue(), 2), 2);

$multiply->convert(2); // 2*2+2=6
$add->convert(2);      // (2+2)*2=8

Next accessors are available for grids now:

  • trim trims a string
  • toUpper converts a string to upper case
  • toLower converts a string to lower case
  • split splits a string using a given char into an array