alex-patterson-webdev / doctrine-query-filter
Query filtering components for Doctrine ORM
Requires
- php: >=8.2
- alex-patterson-webdev/date-time: ^0.6.0
- doctrine/orm: ^2.8
Requires (Dev)
- friendsofphp/php-cs-fixer: ^3.6.0
- mockery/mockery: ^1.6
- phpspec/prophecy: ^1.15.0
- phpstan/phpstan: ^1.8
- phpstan/phpstan-mockery: ^1.1
- phpunit/phpunit: ^9.5
- squizlabs/php_codesniffer: ^3.6
README
Doctrine Query Filter
A package providing query filtering components for Doctrine ORM. By modeling query filter criteria as reusable objects, it offers a consistent and extendable way of constructing complex DQL statements.
The project has been inspired by the Laminas Doctrine QueryBuilder; providing similar functionality without the Laminas Framework dependency.
Installation
Installation via composer.
require alex-patterson-webdev/doctrine-query-filter ^0.9
Usage
Using the QueryFilterManager
we can create DQL strings from an array
format. For example, consider the following DQL string.
SELECT c FROM Customer c WHERE c.forename = 'Fred' AND (c.age BETWEEN 18 AND 30)
We can represent this DQL query using a collection of filters, known as our query criteria
$criteria = [
'filters' => [
[
'name' => 'eq',
'field' => 'forename',
'value' => 'Fred',
],
[
'name' => 'between',
'field' => 'age',
'from' => 18,
'to' => 30
],
],
];
By passing this $criteria
to our QueryFilterManager
we can generate (and execute) the query in the following way.
// Get our Doctrine query builder instance
$queryBuilder = $entityManager->getRepository('Customer')->createQueryBuilder('c');
// Create a new QueryFilterManager (and supply it with a desired FilterFactory instance)
$queryFilterManager = new QueryFilterManager(new FilterFactory());
// Apply the filters to the $queryBuilder
$queryBuilder = $queryFilterManager->filter($queryBuilder, 'Customer', $criteria);
// SELECT c FROM Customer c WHERE c.forename = 'Fred' AND (c.age BETWEEN 18 AND 30)
echo $queryBuilder->getDQL();
// Fetch the results
$results = $queryBuilder->getQuery()->execute();
Combining filters with an OR condition
When defining more than one filter, conditions will be explicitly "AND" together using the and
composite query filter.
To instead create an "OR" condition, we must define a or
filter and provide it with the required conditions
array.
// SELECT c FROM Customer c WHERE c.enabled = :enabled AND (c.username = :username1 OR c.username = :username2)
$criteria = [
'filters' => [
[
'name' => 'eq',
'field' => 'enabled',
'value' => true,
],
[
'name' => 'or',
'conditions' => [
[
'name' => 'eq',
'field' => 'username',
'value' => 'Fred',
],
[
'name' => 'eq',
'field' => 'username',
'value' => 'bob',
],
]
],
],
];
Nesting Filters
You can also nest a combination of the and
and or
, the generated DQL will include the correct grouping.
// WHERE x.surname = 'Smith' OR (x.age > 18 AND x.gender = 'Male')
$criteria = [
'filters' => [
[
'name' => 'or',
'conditions' => [
[
'name' => 'eq',
'field' => 'surname',
'value' => 'Smith',
],
[
'name' => 'and',
'conditions' => [
[
'name' => 'gt',
'field' => 'age',
'value' => 18,
],
[
'name' => 'eq'
'field' => 'gender',
'value' => 'Male',
],
]
],
]
]
],
];
Custom Filters
The above examples demonstrate the use of the built-in filters. However, these are very verbose and can be difficult to manage.
The true power of the QueryFilterManager
is the ability to create and use custom filters; by extending the AbstractFilter
class.
Custom filters are self-contained and reusable across multiple queries. This allows for a more modular and maintainable approach to build complex queries.
The below example demonstrates how we could utilise the provided filters to create our own CustomerSearch
filter that accepts optional $criteria
parameters.
use Arp\DoctrineQueryFilter\Filter\AbstractFilter;
use Arp\DoctrineQueryFilter\Filter\Exception\FilterException;
use Arp\DoctrineQueryFilter\Metadata\MetadataInterface;
use Arp\DoctrineQueryFilter\QueryBuilderInterface;
final class CustomerSearch extends AbstractFilter
{
public function filter(QueryBuilderInterface $queryBuilder, MetadataInterface $metadata, array $criteria): void
{
if (empty($criteria['surname'])) {
throw new FilterException('The surname criteria is required');
}
$filters = [
[
'name' => 'neq',
'field' => 'status',
'value' => 'inactive',
],
[
'name' => 'begins_with',
'field' => 'surname',
'value' => $criteria['surname'],
],
];
if (isset($criteria['forename'])) {
$filters[] = [
'name' => 'eq',
'field' => 'forename',
'value' => $criteria['forename'],
];
}
if (isset($criteria['age'])) {
$filters[] = [
'name' => 'gte',
'field' => 'age',
'value' => (int) $criteria['age'],
];
}
$this->applyFilters($queryBuilder, $metadata, $filters);
}
}
// We must register the custom filter with the FilterFactory
$filterFactory = new FilterFactory();
$filterFactory->addToClassMap('customer_search', CustomerSearch::class);
$queryFilterManager = new QueryFilterManager($filterFactory);
$criteria = [
'filters' => [
[
'name' => 'customer_search',
'surname' => 'Smith',
'age' => 21,
],
],
];
$queryBuilder = $queryFilterManager->filter($queryBuilder, 'Entity\Customer', $criteria);
// Executes DQL: SELECT c FROM Customer c WHERE c.status != 'inactive' AND c.surname LIKE 'Smith%' AND c.age >= 21
$queryBuilder->getQuery()->execute();
Sorting Results
In addition to filtering collections, we can also add sorting by using the sort
criteria key to add Sort Fillers.
// SELECT c FROM Customer c WHERE c.id = 123 ORDER BY c.id DESC, c.createdDate ASC
$critiera = [
'filters' => [
[
'name' => 'eq',
'field' => 'id',
'value' => 123
],
'sort' => [
[
'name' => Field::class,
'field' => 'id',
'direction' => OrderByDirection::DESC->value
],
[
'field' => 'createdDate'
],
]
]
];
Each sort filter requires the field
key, with an optional direction
of ASC
or DESC
.
Omitting the name
key from a sort filter will apply a Arp\DoctrineQueryFilter\Sort\Field
sort filter by default. In addition,
omitting the direction
will by default make the sort direction ASC
.
Filter Reference
There are many types of query filters already included. The table below defines the filter aliases and their available options.
FilterFactory
If you require greater control on the construction of the query filters, it is possible to provide QueryFilter
instances directly to the $criteria['filters']
array instead of using the array format.
$queryFilterManager = new QueryFilterManager(new FilterFactory());
$criteria = [
'filters' => [
$queryFilterManager->createFilter('eq', ['field' => 'surname', 'value => 'Smith']),
$queryFilterManager->createFilter('between', ['field' => 'age', 'from => 18, 'to' => 65]),
],
],
Unit tests
Unit tests can be executed using PHPUnit from the application root directory.
php vendor/bin/phpunit