api-skeletons / doctrine-orm-querybuilder-filter
Filter a QueryBuilder with filter[fieldName|operator]=value
Requires
- php: ~7.4||^8.0
- doctrine/orm: ^2.8
Requires (Dev)
- doctrine/coding-standard: ^9.0
- doctrine/dbal: ^3.1.1
- php-parallel-lint/php-parallel-lint: ^1.3
- phpunit/phpunit: ^9
- symfony/cache: ^5.3
- vimeo/psalm: ^4.14
README
Apply filters to a QueryBuilder based on request parameters. Supports deep queries using joins. This repository is intened to apply query parameters to filter entity data.
Installation
Run the following to install this library using Composer:
composer require api-skeletons/doctrine-orm-querybuilder-filter
Quick Start
use ApiSkeletons\Doctrine\QueryBuilder\Filter\Applicator; $applicator = new Applicator($entityManager, Entity\User::class); $queryBuilder = $applicator($_REQUEST['filter']);
Filters
The pattern for creating a filter is filter[fieldName|operator]=value
This pattern is an easy way to define complex queries utilizing all the filtering
capabilities of the QueryBuilder.
The following URL will provide a LIKE filter for a user's name
:
http://localhost/api/user?filter[name|like]=John
These operators are supported:
- eq - equals. If no operator is specified then this is the default
- neq - does not equal
- gt - greater than
- gte - greater than or equals
- lt - less than
- lte - less than or equals
- between - between two values comma delmited e.g.
filter[id|between]=1,5
- like - a fuzzy search which wraps the value in wildcards
- startswith - a
like
operator with a wildcard on the right - endswith - a
like
operator with a wildcard on the left - in - a list of values to match comma delmited e.g.
filter[id|in]=1,2,3]
- notin - the opposite of an
in
operator - isnull - any value is acceptable; the field will be checked for null
- isnotnull - the opposite of an
isNull
operator - sort - sort the result on the field either
asc
ordesc
You may use as many filters as you wish. Filters operate on the field names of the entity and on the association names. This allows you to filter on an assocaition. For instance, to filter a list of users by company where the id of the company is known and the Doctrine metadata is correct you may filter like this:
http://localhost/api/user?filter[company]=10
So even though there is not a field named company there is an association and that is filterable though this tool.
Filtering on single entities
The configuration of the Applicator allows you to enable assocaition filtering, but this is disabled by default. So, assuming the default setting, this is a complex filter on a single entity:
http://localhost/api/user?filter[company|neq]=15&filter[name]=John
Filtering on the hierarcy of entities
The configuration of the Applicator allows you to enable assocation filtering. This means you can filter the current entity based on fields with an association with the current entity and you may do so as deep as you wish.
In this example we'll pull user data based on their company by name:
http://localhost/api/user?filter[company][name|eq]=AAA
In this example we'll pull user data based on their company by company type by company type name:
http://localhost/api/user?filter[company][companyType][name|neq]=Consultant
Before you get too concerned about this ability remember you can modify the QueryBuilder after it has had filters applied to it so if you need to apply security settings that is supported.
A note on sorting
While sorting isn't by strict definition a filter, it falls into the same context when requesting data. You can sort by multiple fields and you can sort across associations (if enabled). Sorting is prioritized left to right.
Use
This is the minimum required to use this library:
use ApiSkeletons\Doctrine\QueryBuilder\Filter\Applicator; $applicator = (new Applicator($entityManager, Entity\User::class)); $queryBuilder = $applicator($_REQUEST['filter']);
This is an example of configuring the applicator with all possible options
use ApiSkeletons\Doctrine\QueryBuilder\Filter\Applicator; $applicator = (new Applicator($entityManager, Entity\User::class)) ->enableRelationships() ->removeOperator('like') ->setEntityAlias('user') ->setFieldAliases(['firstName' => 'name']) ->setFilterableFields(['id', 'name']) ; $queryBuilder = $applicator($_REQUEST['filter']); $entityAliasMap = $applicator->getEntityAliasMap();
After a QueryBuilder is returned with the filters applied to it you may modify it as desired before using it to fetch your result.
Real world Laravel example
In this example, data from the Entity\Style
entity is returned using
HAL in a paginated response
in a controller action.
use ApiSkeletons\Doctrine\QueryBuilder\Filter\Applicator; use Doctrine\ORM\Tools\Pagination\Paginator; use HAL; use Illuminate\Http\Request; use Illuminate\Pagination\LengthAwarePaginator; public function fetchAll(Request $request) { $filter = $request->query()['filter'] ?? []; if (! is_array($filter)) { $filter = []; ] $page = (int) $request->query()['page'] ?? 1; if ($page < 1) { $page = 1; } $applicator = (new Applicator(app('em'), Entity\Style::class)) ->enableRelationships(true); $queryBuilder = $applicator($filter); $paginator = new Paginator($queryBuilder); $paginator->getQuery() ->setFirstResult(25 * ($page - 1)) // Page is 0 indexed in query ->setMaxResults(25) ; $data = (new LengthAwarePaginator(iterator_to_array($paginator->getIterator()), $paginator->count(), 25)) ->appends($request->query()) ->withPath(route('api.style::fetchAll')) ; return HAL::paginate('style', $data)->toArray(); }
Configuration
Using the Applicator is strait-forward and many options for configuring it are available. Begin by creating the Applicator then run configuration functions:
$applicator = new Applicator($entityManager, Entity\Style::class);
enableAssociations()
This configuration method turns on deep filtering by using the Doctrine metadata to traverse the ORM through existing joins to the target entity. This is only possible in a Doctrine installation with complete metadata and proper associations between entities defined in the metadata.
removeOperator(string|array)
If you want to disable any operator you may remove it. A good example is the
like
operator which could result in expensive queries.
setEntityAlias(string)
The default alias used when creating filters for the target entity in the
QueryBuilder is entity
. You may want to change this so you know the alias
after the QueryBuilder is returned and you can add additional parameters to it.
setFieldAliases(array)
If you want the filter to alias a field instead of using the ORM field name
(such as using naming strategies in hydrators) you may pass an array of
[alias => field]
values so mapping can be adjusted.
setFilterableFields(array)
By default all fields on the target entity can be filtered. If you want to limit which fields your users can create filters for then pass those field names in this array.
getEntityAliasMap()
This method is for post-processing of the target entity. When users use
deep filtering using enableAssociations()
aliases are created for every
entity joined to the original entity query. This method returns an array of
[alias => entityClass]
for all entities joined in the QueryBuilder.