Use the component spiral/data-grid
and spiral/data-grid-bridge
to generate Cycle and DBAL select queries automatically,
based on specifications provided by the end-user.
To install the component:
composer require spiral/data-grid-bridge
Note
that the spiral/framework >= 2.7 already includes this component.
Activate the bootloader Spiral\DataGrid\Bootloader\GridBootloader
in your application after the Database and Cycle
bootloaders:
protected const LOAD = [
// ...
\Spiral\DataGrid\Bootloader\GridBootloader::class,
// ...
];
To use the data grid, you will need two base abstractions - grid factory and grid schema.
Grid Schema is an object which describes how a data selector should be configured based on user input. Use
Spiral\DataGrid\GridSchema
:
use Spiral\DataGrid;
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 DataGrid\GridSchema();
// allow user to paginate the result set with 10 results per page
$schema->setPaginator(new PagePaginator(10));
// allow use to sort by name
$schema->addSorter('id', new Sorter('id'));
// find by matching name, the value supplied by user
$schema->addFilter('name', new Like('name', new StringValue()));
Note
You can extend the GridSchema and initiate all the specifications in the constructor.
To use the defined grid schema, you will have to obtain an instance of a supported data source. By default, the Cycle Select and Database Select Query are supported.
use Spiral\DataGrid\GridFactory;
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();
$schema->setPaginator(new PagePaginator(10));
$schema->addSorter('id', new Sorter('id'));
$schema->addFilter('name', new Like('name', new StringValue()));
/**
* @var App\Database\UserRepository $users
* @var GridFactory $factory
*/
$result = $factory->create($users->select(), $schema);
print_r(iterator_to_array($result));
If you want any of the specifications to be applied by default, you can pass them in the following way:
/** @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]
]);
How to apply the specifications:
?paginate[page]=2
like
filter: ?filter[name]=antony
?sort[id]=desc
?fetchCount=1
Note
These params are defined in theGridFactory
, you can overwrite them.
If you need to count items using a complex function, you can pass a callable function via withCounter
method:
/** @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.
This is a simple page+limit pagination:
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.
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
).
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 orderNext specifications are available for grids for now:
AscSorter
and DescSorter
contain expressions that should be applied with ascending (or descending) sorting
order:
use Spiral\DataGrid\Specification\Sorter;
$ascSorter = new Sorter\AscSorter('first_name', 'last_name');
$descSorter = new Sorter\DescSorter('first_name', 'last_name');
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:
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.
This is a sorter wrapper for a directional sorter in case you have the same fields for sorting in both directions:
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');
This is just a way of combining sorters into one set, passing direction will apply it to the whole set:
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');
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.
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.
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.
This is a union filter for logic and
operation.
Some examples with fixed values:
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:
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);
This is a union filter for logic or
operation.
Examples with fixed values:
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:
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);
These are simple expression filters for logic =
, !=
operations.
Examples with a fixed value:
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:
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');
These are simple expression filters for logic >
, >=
, <
, <=
operations.
Examples with a fixed value:
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:
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');
These are simple expression filters for logic in
, not in
operations.
Examples with a fixed value:
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:
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:
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]
This is a simple expression filter for like
operation.
Examples with a fixed value:
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:
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 is a complex filter representing a map of filters with their own values.
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:
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]);
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 noValueInterface
should be declared.
Example with a single value:
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:
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:
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');
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 supportbetween
operation, that's why the conversion togt/gte
+lt/lte
is used by default.
Between filter has two modifications: field-based and value-based.
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:
#
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
:
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:
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 forValueBetween
filter.
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:
$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 theupcoming
filter.
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:
This value accepts any input and doesn't convert them:
use Spiral\DataGrid\Specification\Value;
$value = new Value\AnyValue();
$value->accepts('123'); // always true
$value->convert('123'); // always equal to the input
This value expects an array and converts all of them according to the base value type. The input should not be empty:
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]
This value expects a bool input, 1/0 (as int or strings), and true
/false
strings:
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
These values are supposed to check your input if it is positive/negative/non-positive/non-negative according to the base value type:
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
Applies numeric values, also empty strings (zero is also a value):
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
This value expects a string representing a timestamp or a datetime and converts it into a \DateTimeImmutable
:
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
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:
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
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:
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
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:
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]
This value is based on an enum value, the difference is that all of the array input elements should match the given enum array:
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]
Applies string-like input, also empty strings (if a corresponding constructor param is passed):
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
Applies scalar values, also empty strings (if a corresponding constructor param is passed):
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
Applies string-like input and checks if it matches the given regex pattern, converts to string:
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'
Applies UUID-formatted strings, a user can choose which validation pattern to use:
The output is converted to string.
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
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:
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
If you need to check first that the value isn't empty, wrap the value with NotEmpty()
:
use Spiral\DataGrid\Specification\Value;
$int = new Value\IntValue();
$notEmpty = new Value\NotEmpty($int);
$int->accepts(0); // true
$notEmpty->accepts(0); // false
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:
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:
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 stringtoUpper
converts a string to upper casetoLower
converts a string to lower casesplit
splits a string using a given char into an array