README

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.

Table of Contents

• Installation
• Usage
  • Initialization
  • With Doctrine Query Builder
  • With simple SQL
• Supported filters
  • Equals
  • Not Equals
  • Greater than
  • Greater than or Equals
  • Lower than
  • Lower than or Equals
  • IN
• Tuples in filters
• Examples
  • IN + EQ
  • GT + LT (between)
  • EQ Tuple
• Functions in filters
  • Example for fullName function
  • Function parameters definition
    • By string
    • By array
    • By object
    • Combinations
  • Register and Execute function
• Exceptions and error handling
• Development

Installation

composer require lmc/api-filter

Usage

For example lets have query parameters from following request

GET http://host/endpoint/?field=value

Initialization

// 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'
//         }
//     }
// ]

With Doctrine Query Builder Applicator

• requires doctrine/orm installed
• applying filters uses cloned QueryBuilder -> original QueryBuilder is untouched

Example

// in EntityRepository/Model
$queryBuilder = $this->createQueryBuilder('alias');
$queryBuilder = $apiFilter->applyFilters($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();

Shorter example (same as ☝)

// in EntityRepository/Model
$queryBuilder = $this->createQueryBuilder('alias');

$apiFilter
    ->applyFilters($filters, $queryBuilder)                                     // query builder with applied filters
    ->setParameters($apiFilter->getPreparedValues($filters, $queryBuilder)) // ['field_eq' => 'value']
    ->getQuery();

With SQL Applicator

• ❗it is just a naive implementation and should be used carefully❗
• it still might be used on simple SQLs without ORDER BY, GROUP BY etc. because it simply adds filters as a WHERE conditions

SQL Applicator must be registered explicitly

// in DI container
$apiFilter->registerApplicator(new SqlApplicator(), Priority::MEDIUM);

Example

// 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);

Shorter example (same as ☝)

// in EntityRepository/Model
$sql = 'SELECT * FROM table';
$stmt = $connection->prepare($apiFilter->applyFilters($filters, $sql)); // SELECT * FROM table WHERE 1 AND field = :field_eq 
$stmt->execute($apiFilter->getPreparedValues($filters, $sql));      // ['field_eq' => 'value']

Supported filters

Equals - EQ (=)

GET http://host/endpoint/?field[eq]=value
GET http://host/endpoint/?field=value

Both examples ☝ are equal

Not Equals - NEQ (!=)

GET http://host/endpoint/?field[neq]=value

Greater Than - GT (>)

GET http://host/endpoint/?field[gt]=value

Greater Than Or Equals - GTE (>=)

GET http://host/endpoint/?field[gte]=value

Lower Than - LT (<)

GET http://host/endpoint/?field[lt]=value

Lower Than Or Equals - LTE (<=)

GET http://host/endpoint/?field[lte]=value

IN

GET http://host/endpoint/?type[in][]=one&type[in][]=two

• Tuples are not allowed in IN filter

Function

GET http://host/endpoint?fullName=(Jon,Snow)

• there is much more options and possibilities with functions which you can see here

Tuples in filters

Tuples

• are important in filters if you have some values, which must be sent together
• are composed of two or more values (Tuple of one value is just a value)
• items must be in ( ) and separated by ,
  • array in Tuple must be in [ ] and items separated by ;
• it is advised NOT to use a space between values because of the URL specific behavior
• for more information about Tuples see https://github.com/MortalFlesh/MFCollectionsPHP#immutabletuple

Column with Tuple

Columns declared by Tuple behaves the same as a single value but its value must be a Tuple as well. Columns can contain a filter specification for each value.

• default filter is EQ for a single value and IN for an array of values (in Tuple)

Values with Tuple

Values in the Tuple must have the same number of items as is the number of columns. Values can contain a filter specification for all values in a Tuple.

❗NOTE: A filter specification must not be in both columns and values.

Usage

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".

Examples

❗For simplicity of examples, they are shown on the SQL Applicator which is NOT auto-registered❗

IN + EQ filter

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',
// ]

GT + LT filter (between)

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]

EQ with Tuple

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']

More Examples

Equals (implicit and explicit)

GET http://host/person/?fullName=Jon Snow
GET http://host/person/?fullName[eq]=Jon Snow

Result:

-   column: fullName
    filters: eq
    value: Jon Snow

Multiple filters (implicit and explicit)

By single values

GET http://host/person/?firstName=Jon&surname=Snow
GET http://host/person/?firstName[eq]=Jon&surname[eq]=Snow

By Tuples

GET http://host/person/?(firstName,surname)=(Jon,Snow)
GET http://host/person/?(firstName,surname)[eq]=(Jon,Snow)
GET http://host/person/?(firstName[eq],surname[eq])=(Jon,Snow)

Result:

-   column: firstName
    filters: eq
    value: Jon

-   column: surname
    filters: eq
    value: Snow

Multiple filters

You can mix all types of filters (tuples, explicit, implicit).

Perfect wife by generic filters

By single values

GET http://host/person/?age[gte]=18&age[lt]=30&category[in][]=serious&category[in][]=marriage&sense-of-humor=true

By Tuples

GET http://host/person/?(age[gte],age[lt],category,sense-of-humor)=(18,30,[serious;marriage],true)

Result:

-   column: age
    filters: gte
    value: 18

-   column: age
    filters: lt
    value: 30

-   column: category
    filters: in
    value: [ serious, marriage ]

-   column: sense-of-humor
    filters: eq
    value: true

Want to see movies by generic filters

By single values

GET http://host/movie/?year[gte]=2018&rating[gte]=80&genre[in][]=action&genre[in][]=fantasy

By Tuples

GET http://host/movie/?(year[gte],rating[gte],genre)=(2018,80,[action;fantasy])

Result:

-   column: year
    filters: gte
    value: 2018

-   column: rating
    filters: gte
    value: 80

-   column: genre
    filters: in
    value: [ action, fantasy ]

Functions in filters

With function you can handle all kinds of problems, which might be problematic with just a simple filters like eq, etc.

Example for fullName function

Let's see how to work with functions and what is required to do. We will show it right on the example.

Expected api

GET http://host/endpoint?fullName=(Jon,Snow)

☝️ example above shows what we want to offer to our consumers. It's easy and explicit enough.

It may even hide some inner differences, for example with simple filters, database column must have same name as field in filter, but with function, we can change it.

Let's say that in database we have something like:

type Person = {
    first_name: string
    lastname: string
}

Initialization

First of all, you have to define functions you want to use.

// in DI container/factory
$apiFilter = new ApiFilter();

$apiFilter->declareFunction(
    'fullName',
    [
        new ParameterDefinition('firstName', 'eq', 'first_name'),   // parameter name and field name are different, so we need to define it
        'lastname`,              // parameter name and field name are the same and we use the implicit `eq` filter, so it is defined simply 
    ]
);

Method declareFunction will create a function with filters based on parameters.
There is also registerFunction method, which allows you to pass any function you want. This may be useful when you dont need filter functionality at all or have some custom storage, etc.

Parsing and applying filters

Now when request with ?fullName=(Jon,Snow) come, ApiFilter can parse it to:

// in service/controller/...
$sql = 'SELECT * FROM person';

$filters = $apiFilter->parseFilters($request->query->all());
// [
//      0 => Lmc\ApiFilter\Filter\FilterFunction {
//        private $title  => 'function'
//        private $column => 'fullName'
//        private $value  => Lmc\ApiFilter\Entity\Value {
//          private $value => Closure
//        }
//      },
//
//      1 => Lmc\ApiFilter\Filter\FunctionParameter {
//        private $title => 'function_parameter'
//        private $column => 'firstName'
//        private $value => Lmc\ApiFilter\Entity\Value {
//          private $value => 'Jon'
//        }
//      },
//
//      2 => Lmc\ApiFilter\Filter\FunctionParameter {
//        private $title => 'function_parameter'
//        private $column => 'lastname'
//        private $value => Lmc\ApiFilter\Entity\Value {
//          private $value => 'Snow'
//        }
//      }
// ]

$appliedSql = $apiFilter->applyFilters($filters, $sql);
// SELECT * FROM person WHERE first_name = :firstName_function_parameter AND lastname = :lastname_function_parameter

$preparedValues = $apiFilter->getPreparedValues($filters, $sql);
// [
//      'firstName_function_parameter' => 'Jon',
//      'lastname_function_parameter' => 'Snow',
// ]

Supported function usage

All examples below results the same. We have that many options, so we can allow as many different consumers as possible.

### Explicit function call
GET http://host/endpoint?fullName=(Jon,Snow)

### Explicit function call with values 
GET http://host/endpoint?function=fullName&firstName=Jon&lastname=Snow

### Implicit function call by values
GET http://host/endpoint?firstName=Jon&lastname=Snow

### Explicit function call by tuple 
GET http://host/endpoint?(function,firstName,surname)=(fullName, Jon, Snow)

### Implicit function call by tuple
GET http://host/endpoint?(firstName,surname)=(Jon, Snow)

### Explicit function call by filter parameter 
GET http://host/endpoint?filter[]=(fullName,Jon,Snow)

Function Parameters Definition

To declare or register function, you have to define its parameters. There are many ways/needs to do it.

Defined as string

This is the easiest way to do it. You just define a name.

It means:

• you want eq filter (or IN for array) and the column name and parameter name are the same
• the value for this parameter is mandatory

$apiFilter->declareFunction('fullName', ['firstName', 'surname']);

Defined as array

This allows you to pass more options for a paramater.

Only one item

If you declare it just by giving the only item, it is the same as definition by string above.

$apiFilter->declareFunction('fullName', [['firstName'], ['surname']]);

More than one item

It means

• firstName parameter uses eq filter, has first_name column in storage and is mandatory
• surname parameter uses eq filter, has lastname column in storage and its value is Snow (which will always be used and no value can override it)

$apiFilter->declareFunction('fullName', [
    ['firstName', 'eq', 'first_name'],
    ['surname', 'eq', 'lastname', 'Snow']
]);

Defined as object

This allows you to pass same options as with the array, but explicitly defined object. (It even has some special constructor methods to simplify creation)

$apiFilter->declareFunction('fullName', [
    new ParameterDefinition('firstName', 'eq', 'first_name'),
    new ParameterDefinition('surname', 'eq', 'lastname, new Value('Snow'))
]);

Combinations

All options can be combined to best suite the parameter.

Declaration

$apiFilter->declareFunction('fullNameGrownMan', [
    ['firstName', 'eq', 'first_name'],
    'surname',
    ['age', 'gte', 'age', 18],
    ParameterDefinition::equalToDefaultValue('gender', new Value('male')),
]);

Usage

GET http://endpoint/host?fullNameGrownMan=(Jon,Snow)

Register and Execute function

Example below is just for explicit demonstration, you should probably never allow execute SQL queries like this.

Usage in PHP

// in DI container/factory
$apiFilter = new ApiFilter();

$apiFilter->registerFunction(
    'sql',
    ['query'],
    function (\PDO $client, FunctionParameter $query): \PDOStatement {
        return $client->query($query->getValue()->getValue());
    }
);

// in service/controller/...
$statement = $apiFilter->executeFunction('sql', $queryParameters, $client);    // \PDOStatement

$statement->execute();
// fetch result, etc...

Usage of the API

All examples below results the same. We have that many options, so we can allow as many different consumers as possible.

### Explicit function call
GET http://endpoint/host?sql=SELECT * FROM person

### Explicit function call with values
GET http://host/endpoint?function=sql&query=SELECT * FROM person

### Implicit function call by values
GET http://host/endpoint?query=SELECT * FROM person

### Explicit function call by tuple
GET http://host/endpoint?(function,query)=(sql, SELECT * FROM person)

### Explicit function call by filter parameter
GET http://host/endpoint?filter[]=(sql, SELECT * FROM person)

Exceptions and error handling

Known exceptions occurring inside ApiFilter implements Lmc\ApiFilter\Exception\ApiFilterExceptionInterface. The exception tree is:

Please note if you register a custom applicator to the ApiFilter (via $apiFilter->registerApplicator()), it may throw other exceptions which might not implement ApiFilterExceptionInterface.

Development

Install

composer install

Tests

composer all

Todo

• defineAllowed: (this should be on DI level)
  • Fields (columns)
  • Filters
  • Values
• add more examples:
  • different configuration per entity/table