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:
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.
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
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:
private SerializerInterface $serializer;
public function __construct()
{
$this->serializer = new Serializer([new ObjectNormalizer()]);
}
First of all, we build a symfony serializer in the constructor since we'll need it in the next lines.
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.
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:
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:
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:
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:
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
Top comments (0)