Parser/builder for filters from API query parameters.
It is just a parser/builder for filters, it is not a place for business logic so it should be wrapped by your class if you want to be more strict about filters. Same if you want different settings per entity/table, it should be done by a specific wrapper around this library.
composer require lmc/api-filterFor example lets have query parameters from following request
GET http://host/endpoint/?field=value// in DI container/factory
$apiFilter = new ApiFilter();
$apiFilter->registerApplicator(...); // optional, when you want to use non-standard implementation
// in service/controller/...
$filters = $apiFilter->parseFilters($request->query->all());
// [
// 0 => Lmc\ApiFilter\Filter\FilterWithOperator {
// private $title => 'eq'
// private $operator => '='
// private $column => 'field'
// private $value => Lmc\ApiFilter\Entity\Value {
// private $value => 'value'
// }
// }
// ]- requires
doctrine/orminstalled - applying filters uses cloned
QueryBuilder-> originalQueryBuilderis untouched
// in EntityRepository/Model
$queryBuilder = $this->createQueryBuilder('alias');
$queryBuilder = $apiFilter->applyAll($filters, $queryBuilder);
// or one by one
foreach ($filters as $filter) {
$queryBuilder = $apiFilter->applyFilter($filter, $queryBuilder);
}
// get prepared values for applied filters
$preparedValues = $apiFilter->getPreparedValues($filters, $queryBuilder); // ['field_eq' => 'value']
// get query
$queryBuilder
->setParameters($preparedValues)
->getQuery();// in EntityRepository/Model
$queryBuilder = $this->createQueryBuilder('alias');
$apiFilter
->applyAll($filters, $queryBuilder) // query builder with applied filters
->setParameters($apiFilter->getPreparedValues($filters, $queryBuilder)) // ['field_eq' => 'value']
->getQuery();- ❗it is just a naive implementation and should be used carefully❗
- it still might be used on simple
SQLs withoutORDER BY,GROUP BYetc. because it simply adds filters as aWHEREconditions
SQL Applicator must be registered explicitly
// in DI container
$apiFilter->registerApplicator(new SqlApplicator(), Priority::MEDIUM);// in Model/EntityRepository
$sql = 'SELECT * FROM table';
$sql = $apiFilter->applyFilters($filters, $sql); // "SELECT * FROM table WHERE 1 AND field = :field_eq"
// or one by one
foreach ($filters as $filter) {
$sql = $apiFilter->applyFilter($filter, $sql);
}
// get prepared values for applied filters
$preparedValues = $apiFilter->getPreparedValues($filters, $sql); // ['field_eq' => 'value']
// execute query
$stmt = $connection->prepare($sql);
$stmt->execute($preparedValues);// in EntityRepository/Model
$sql = 'SELECT * FROM table';
$stmt = $connection->prepare($apiFilter->applyAll($filters, $sql)); // SELECT * FROM table WHERE 1 AND field = :field_eq
$stmt->execute($apiFilter->getPreparedValues($filters, $sql)); // ['field_eq' => 'value']GET http://host/endpoint/?field[eq]=value
GET http://host/endpoint/?field=valueBoth examples ☝ are equal
GET http://host/endpoint/?field[gt]=valueGET http://host/endpoint/?field[gte]=valueGET http://host/endpoint/?field[lt]=valueGET http://host/endpoint/?field[lte]=valueGET http://host/endpoint/?type[in][]=one&type[in][]=twoTuplesare not allowed inINfilter
Tuples
- are important in filters if you have some values, which must be sent together
- are composed of two or more values (
Tupleof one value is just a value) - items should be in
()and separated by, - it is advised NOT to use a space between values because of the URL specific behavior
- for more information about
Tuplessee https://github.com/MortalFlesh/MFCollectionsPHP#immutabletuple
Columns declared by Tuple behaves the same as a single value but its value must be a Tuple as well.
Values in the Tuple must have the same number of items as is the number of columns.
GET http://host/endpoint/?(first,second)[eq]=(one,two) ☝ means that you have two columns first and second and they must be sent together.
Column first must equal the value "one" and column second must equal the value "two".
❗For simplicity of examples, they are shown on the SQL Applicator which is NOT auto-registered❗
GET http://host/person/?type[in][]=student&type[in][]=admin&name=Tom$parameters = $request->query->all();
// [
// "type" => [
// "in" => [
// 0 => "student"
// 1 => "admin"
// ]
// ],
// "name" => "Tom"
// ]
$filters = $apiFilter->parseFilters($parameters);
$sql = 'SELECT * FROM person';
foreach ($filters as $filter) {
$sql = $apiFilter->applyFilter($filter, $sql);
// 0. SELECT * FROM person WHERE 1 AND type IN (:type_in_0, :type_in_1)
// 1. SELECT * FROM person WHERE 1 AND type IN (:type_in_0, :type_in_1) AND name = :name_eq
}
$preparedValues = $apiFilter->getPreparedValues($filters, $sql);
// [
// 'type_in_0' => 'student',
// 'type_in_1' => 'admin',
// 'name_eq' => 'Tom',
// ]GET http://host/person/?age[gt]=18&age[lt]=30$parameters = $request->query->all();
// [
// "age" => [
// "gt" => 18
// "lt" => 30
// ],
// ]
$filters = $apiFilter->parseFilters($parameters);
$sql = 'SELECT * FROM person';
$sql = $apiFilter->applyFilters($filters, $sql); // SELECT * FROM person WHERE 1 AND age > :age_gt AND age < :age_lt
$preparedValues = $apiFilter->getPreparedValues($filters, $sql); // ['age_gt' => 18, 'age_lt' => 30]GET http://host/person/?(firstname,surname)=(John,Snow)$parameters = $request->query->all(); // ["(firstname,surname)" => "(John,Snow)"]
$sql = 'SELECT * FROM person';
$filters = $apiFilter->parseFilters($parameters);
// [
// 0 => Lmc\ApiFilter\Filter\FilterWithOperator {
// private $title => "eq"
// private $operator => "="
// private $column => "firstname"
// private $value => Lmc\ApiFilter\Entity\Value {
// private $value => "John"
// }
// },
// 1 => Lmc\ApiFilter\Filter\FilterWithOperator {
// private $title => "eq"
// private $operator => "="
// private $column => "surname"
// private $value => Lmc\ApiFilter\Entity\Value {
// private $value => "Snow"
// }
// }
// ]
$sql = $apiFilter->applyFilters($filters, $sql); // SELECT * FROM person WHERE 1 AND firstname = :firstname_eq AND surname = :surname_eq
$preparedValues = $apiFilter->getPreparedValues($filters, $sql); // ['firstname_eq' => 'John', 'surname_eq' => 'Snow']composer installcomposer all- defineAllowed: (this should be on DI level)
- Fields (columns)
- Filters
- Values
- add more examples:
- different configuration per entity/table