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

alex-patterson-webdev / doctrine-query-filter

Maintainers

Details