Filter Pattern to break down big queries

Note: Some of the optimizations use Symfony-specific functions, but you can find something similar (or build your own) to achieve the same functionality.

When starting to code, very soon you will come to database queries. And after a while you will notice that your queries are getting bigger and bigger. And maybe they become a bit complex and then you realize every time you want to add a new query condition, you need to remember to change it in different places.

// A query function that packs all the query parameters
// in a single function becomes increasingly difficult to maintain

public function findJobsByCriteria(
    ?string $jobName = null,
    ?string $startDateEarliest = null,
    ?string $startDateLatest = null,
    ?string $page = null,
    ?string $pageSize = null,
): array {
    $queryBuilder = $this->createQueryBuilder('j');

    if ($jobName !== null) {
        $queryBuilder
            ->andWhere('j.name LIKE :jobName')
            ->setParameter('jobName', '%'.$jobName.'%')
        ;
    }

    if ($startDateEarliest !== null) {
        $queryBuilder
            ->andWhere('j.startAt >= :startDateEarliest')
            ->setParameter('startDateEarliest', $startDateEarliest)
        ;
    }

    if ($startDateLatest !== null) {
        $queryBuilder
            ->andWhere('j.startAt = :startDateLatest')
            ->setParameter('startDateLatest', $startDateLatest)
        ;
    }

    $queryBuilder
        ->setFirstResult(($page - 1) * $pageSize)
        ->setMaxResults($pageSize)
    ;

    return $queryBuilder->getQuery()->getResult();
}

Does this look familiar to you? Sure, in the beginning, this is still manageable. But what if you want to filter the company name? Or the location? Or the indicated pay?

I've been in more than one project where this single "filterByCriteria" function has surpassed 500 or even 1.000 lines of code. And in some cases, whenever I want to add a query / filter, I need to remember to change other parts of the code, too.

So in this case, what we want to do is break down each filter to be only one (or one set of) filters.

Breaking down into parts

Let's take the jobName filter and put it into its own separate classes. It might look like:

class JobNameFilter implements QueryFilter
{
    public function apply(QueryBuilder $queryBuilder, array $queryParameters): QueryBuilder
    {
        if (!isset($queryParameters['jobName'])) {
            return $queryBuilder;
        }

        $jobName = $queryParameters['jobName'];

        return $queryBuilder
            ->andWhere('j.name LIKE :jobName')
            ->setParameter('jobName', '%'.$jobName.'%')
        ;
    }
}

Cool! It already feels more manageable. The tests for each filter will definitely be easier already, because we can at least test each filter on its own as well, and not the whole super-big-query each time.

And once we separated our filters into separate classes, our super-query might look like this:

// Inject all the filters by dependency injection and
// then go through the filters one by one

public function findJobsByCriteria(array $queryParameters): array {
    $queryBuilder = $this->createQueryBuilder('j');

    $this->jobNameFilter->applyFilter($queryBuilder, $queryParameters)
    $this->startDateFilter->applyFilter($queryBuilder, $queryParameters)
    $this->pageFilter->applyFilter($queryBuilder, $queryParameters)

    return $queryBuilder->getQuery()->getResult();
}

It already looks better!

You know what's kind of annoying, though? Every time you add a new filter, you need to remember to put it into the repository as well. And what if you want to delete a filter? You need to delete it in the repository as well. Otherwise you risk an exception (but optimally, this would of course be caught by a functional test).

This gets even more annoying, if you're using it in more than just one function. So what's next?

Using an interface

Did you see the implements QueryFilter part in the JobNameFilter? Here's something cool you can do with Symfony's autoconfiguration:

#[AutoconfigureTag('app.query_filter')]
interface QueryFilter
{
    public function apply(QueryBuilder $queryBuilder, array $queryParameters): QueryBuilder;
}

And now in the class that uses the query, instead of injecting, then calling the filters one by one you can call all the filters that implement QueryFilter (and thus got the 'app.query_filter' tag) by doing:

public function __construct(
    #[TaggedIterator('app.query_filter')]
    private iterable $queryFilters,
) {
}

public function findJobsByCriteria(array $queryParameters): array {
    $queryBuilder = $this->createQueryBuilder('j');

    foreach ($this->queryFilters as $queryFilter) {
        $queryFilter->apply($queryBuilder, $queryParameters);
    }

    return $queryBuilder->getQuery()->getResult();
}

Whoa! That's already much easier! If you want to add a new query parameter, just create a new class that implements QueryFilter, and it should directly be available in the repository!!

But now, what if you want to create separate type of filters for different types of classes / entities / models?

You can add some configuration to the interface. (You might have seen this type of setup if you have worked with Symfony Voters.)

public function supports(string $class, array $queryParameters): bool
{
    return $class === Job::class && isset($queryParameters['jobName']);
}

/**
 * @param class-string $class
 */
public function apply(QueryBuilder $queryBuilder, string $class, array $queryParameters): QueryBuilder
{
    $columnValue = $queryParameters['jobName'];

    $alias = $queryBuilder->getRootAliases()[0]; // This keeps it generic

    return $queryBuilder
        ->andWhere($alias.'.name LIKE :jobName')
        ->setParameter('jobName', '%'.$jobName.'%')
    ;
}

Now the current result looks like this:

public function __construct(
    #[TaggedIterator('app.query_filter')]
    private iterable $queryFilters,
) {
}

public function findJobsByCriteria(array $queryParameters): array {
    $queryBuilder = $this->createQueryBuilder('j');

    foreach ($this->queryFilters as $queryFilter) {
        if ($queryFilter->supports(Job::class, $queryParameters)) {
            $queryFilter->apply($queryBuilder, $class, $queryParameters);
        }
    }

    return $queryBuilder->getQuery()->getResult();
}

or better yet, you abstract it away in a separate service for easier maintenance:

readonly class QueryFilter
{
    public function __construct(
        #[TaggedIterator('app.query_filter')]
        private iterable $queryFilters,
    ) {}

    public function applyFilters(QueryBuilder $queryBuilder, string $class, array $queryParameters): QueryBuilder
    {
        /** @var QueryFilterInterface $queryFilter */
        foreach ($this->queryFilters as $queryFilter) {
            if ($queryFilter->supports($class, $queryParameters)) {
                $queryFilter->apply($queryBuilder, $class, $queryParameters);
            }
        }

        return $queryBuilder;
    }
}

which leads us to the final result:

public function __construct(
    private QueryFilter $queryFilter,
) {
}

public function findJobsByCriteria(array $queryParameters): array {
    $queryBuilder = $this->createQueryBuilder('j');

    $queryFilter->apply($queryBuilder, $class, $queryParameters);

    return $queryBuilder->getQuery()->getResult();
}

Tada! And we're done. So now we can create generic filters that only get triggered depending on the class name we supply it, and the function will automatically be available in all the relevant repositories.