Adding criteria to your doctrine queries easily
Tags: doctrine, php, query, criteria
Published: 2023-05-13 14:19:27

In this post, I would like to show you how we can easily add criterias to our doctrine queries. 
Let's start by creating a model which holds data we want to add as criteria to our query:

```php
class ListContractsInput
{
    #[Assert\DateTime(message: 'Start at must be a valid datetime')]
    private ?string $startAt = null;

    #[Assert\DateTime(message: 'End at must be a valid datetime')]
    private ?string $endAt = null;

    public function getStartAt(): ?string
    {
        return $this->startAt;
    }

    public function setStartAt(?string $startAt): void
    {
        $this->startAt = $startAt;
    }

    public function getEndAt(): ?string
    {
        return $this->endAt;
    }

    public function setEndAt(?string $endAt): void
    {
        $this->endAt = $endAt;
    }
}
```
As we can see in this model, it holds _startAt_ and _endAt_ values and it requires them to be valid datetimes. More information about symfony validation [here](https://symfony.com/doc/current/validation.html).

Let's see now how we can process this input data to add it as criteria to our queries.

### Creating a base class which process input data

```php
abstract class QueryBuilderCriteriaManager
{
    private SerializerInterface $serializer;

    public function __construct()
    {
        $this->serializer = new Serializer([new ObjectNormalizer()]);
    }

    /**
     * @throws ExceptionInterface
     */
    public function addCriteria(QueryBuilder $qb, string $alias, iterable|object $filters): void
    {
        if(!is_object($filters)){
            foreach ($filters as $key => $value) {
                $this->addToQb($qb, $alias, $key, $value);
            }
        }
        else{
            $criteriaData = $this->serializer->normalize($filters);
            foreach ($criteriaData as $propName => $value) {
                $this->addToQb($qb, $alias, $propName, $value);
            }
        }
    }

    protected function addToQb(QueryBuilder $qb, string $alias, string $key, mixed $value): void
    {
        $method = u('get_' . $key . 'Criteria')->camel()->toString();

        if( !empty($value) || $value === 0 || $value === '0' || $value === false) {
            if(method_exists($this, $method)){
                $this->$method($qb, $alias, $value);
            }
            else{
                $qb
                    ->andWhere($qb->expr()->eq("{$alias}.{$key}", ':' . $key))
                    ->setParameter($key, $value)
                ;
            }
        }

    }

    /**
     * @throws \Exception
     */
    protected function getAsDateTime(string|\DateTimeImmutable $date): \DateTimeImmutable
    {
        return ($date instanceof \DateTimeImmutable)
            ? $date
            : new \DateTimeImmutable($date)
            ;
    }
}
```
Let's analyze this code step by step:

```php
private SerializerInterface $serializer;

public function __construct()
{
    $this->serializer = new Serializer([new ObjectNormalizer()]);
}
```
First of all, we build a [symfony serializer](https://symfony.com/doc/current/components/serializer.html) in the constructor since we'll need it in the next lines.

```php
public function addCriteria(QueryBuilder $qb, string $alias, iterable|object $filters): void
{
    if(!is_object($filters)){
         foreach ($filters as $key => $value) {
            $this->addToQb($qb, $alias, $key, $value);
         }
    }
    else{
         $criteriaData = $this->serializer->normalize($filters);
         foreach ($criteriaData as $propName => $value) {
            $this->addToQb($qb, $alias, $propName, $value);
         }
    }
}
```

Now we create method _addCriteria_ which receives the following parameters:

- _$qb_: QueryBuilder object to which we will add criteria.
- _$alias_: Query main entity alias
- _$filters_: Criteria we're going to add. It can be an object (like our model) or an iterable

If _$filters_ parameter is an iterable, it loops over the iterable as key / values and use method _addToQb_ to add criteria.

If _$filters_ parameter is an object, it first serializes the object to an array and then loops it as key / value and also use _addToQb_ to add criteria.

```php
protected function addToQb(QueryBuilder $qb, string $alias, string $key, mixed $value): void
{
    $method = u('get_' . $key . 'Criteria')->camel()->toString();

    if( !empty($value) || $value === 0 || $value === '0' || $value === false) {
        if(method_exists($this, $method)){
            $this->$method($qb, $alias, $value);
        }
        else{
            $qb
               ->andWhere($qb->expr()->eq("{$alias}.{$key}", ':' . $key))
               ->setParameter($key, $value)
            ;
        }
    }

}
```
Method _addToQb_ receives the following parameters:

- _$qb_: Query builder to which we will add criteria
- _$alias_: Query main entity alias
- _$key_: Field (entity property) we're going to filter
- _$value_: Value by which we want to filter

> Before explaining _addToQb_, I would like to highlight that this class _QueryBuilderCriteriaManager_ is an abstract class and it will be extended by other classes which will define the logic for each criteria we want to add. 

> Each of this child classes (we will see an example later) will define a method for each criteria following this format: _get{KeyName}Criteria_

> For instance, according to the model we did show at the begining of this post, we should define methods _getStartAtCriteria()_ and _getEndAtCriteria()_

Knowing this, let's see how the method works

- First, it builds the method's name following the format we've just seen.
- Second, if value is not null:
   - if method exists in child class, it executes the method which receives _QueryBuider_,_alias_, and _value_ and finally adds the criteria.
   - if method does not exist, it adds the criteria as an equal condition.

### Creating the child class

Let's see now how our child class looks like:

```php
class ContractsCriteriaManager extends QueryBuilderCriteriaManager
{
    /**
     * @throws \Exception
     */
    public function getStartAtCriteria(QueryBuilder $qb, string $alias, string|\DateTimeImmutable $value): void
    {
        $qb
            ->andWhere($qb->expr()->gte("{$alias}.createdAt",':start_at'))
            ->setParameter('start_at', $this->getAsDateTime($value))
        ;
    }

    /**
     * @throws \Exception
     */
    public function getEndAtCriteria(QueryBuilder $qb, string $alias, string|\DateTimeImmutable $value): void
    {
        $qb
            ->andWhere($qb->expr()->lte("{$alias}.createdAt",':end_at'))
            ->setParameter('end_at', $this->getAsDateTime($value))
        ;
    }
}
```
As we can see, child class define two methods following the last format:

- _getStartAtCriteria()_: It adds a criteria so that _createdAt_ must be greater than or equal to _startAt_ value
- _getEndAtCriteria()_: It adds a criteria so that _createdAt_ must be less than or equal to _endAt_ value

### Using it in the repository

Let's take a look to the following repository method:

```php
public function getList(array|object $criteria, ?int $limit): array
{
     $criteriaManager = new ContractsCriteriaManager();
     $qb = $this->createQueryBuilder(self::ALIAS);

     if($limit){
         $qb->setMaxResults($limit);
     }

     $qb->orderBy(self::ALIAS . '.createdAt', 'desc');
     $criteriaManager->addCriteria($qb, self::ALIAS, $criteria);
     return $qb->getQuery()->getResult();
}
```

As you can see, after adding _limit_ and _order_ _by_ to the query builder instance, we use criteria manager to fill our query builder with the criteria data holded in _$criteria_ object.

If we would execute this _getList()_ method and would debug query we would see the following DQL:

```shell
SELECT c FROM App\Entity\Contract c WHERE c.startAt >= :start_at AND c.endAt <= :end_at ORDER BY c.createdAt desc
```

If we would dump query parameters, they would look like this:

```shell
Doctrine\Common\Collections\ArrayCollection {#721
  -elements: array:2 [
    0 => Doctrine\ORM\Query\Parameter {#682
      -name: "start_at"
      -value: DateTimeImmutable @1683128700 {#671
        date: 2023-05-03 15:45:00.0 UTC (+00:00)
      }
      -type: "datetime_immutable"
      -typeSpecified: false
    }
    1 => Doctrine\ORM\Query\Parameter {#681
      -name: "end_at"
      -value: DateTimeImmutable @1683304255 {#670
        date: 2023-05-05 16:30:55.0 UTC (+00:00)
      }
      -type: "datetime_immutable"
      -typeSpecified: false
    }
  ]
}
```

That's all, I hope it can be useful to create your custom criteria behaviour and can create complex queries in a more decoupled way.

You can download this code from my [github repository](https://github.com/icolomina/qb_criteria)

> If you enjoy my content and like the Symfony framework, consider reading my book: [Building an Operation-Oriented Api using PHP and the Symfony Framework: A step-by-step guide](https://amzn.eu/d/3eO1DDi)