Custom QueryBuilder

[PedroTroller]

Hi guys.

In one of my projects I used a quite different division of the repository to which I used to do. The idea was to define the various states of my entity and then chaining these states in the repository to have a code clearer and extensible. I give you a exemble.

This is a simple entity.

<?php

namespace App\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="App\Entity\ProductRepository")
 */
class Product
{

    const STATUS_APPROVED = 1;
    const STATUS_REJECTED = 0;

    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    protected $id;

    /**
     * @ORM\Column(type="string")
     */
    protected $name;

    /**
     * @ORM\Column(type="string")
     */
    protected $createdBy;

    /**
     * @ORM\Column(type="datetime")
     */
    protected $createdAt;

    /**
     * @ORM\Column(type="datetime")
     */
    protected $removedAt;

    /**
     * @ORM\Column(type="integer")
     */
    protected $status;

    public function getName()
    {
        return $this->name;
    }

    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    public function getCreatedBy()
    {
        return $this->createdBy;
    }

    public function setCreatedBy($createdBy)
    {
        $this->createdBy = $createdBy;

        return $this;
    }

    public function getCreatedAt()
    {
        return $this->createdAt;
    }

    public function setCreatedAt($createdAt)
    {
        $this->createdAt = $createdAt;

        return $this;
    }

    public function getRemovedAt()
    {
        return $this->removedAt;
    }

    public function setRemovedAt($removedAt)
    {
        $this->removedAt = $removedAt;

        return $this;
    }

    public function getStatus()
    {
        return $this->removedAt;
    }

    public function setStatus($status)
    {
        $this->status = $status;

        return $this;
    }

}

Now, I want to add a method to her repository to recieved all displayable entites.

My customer told me that a displayable product should be active (should be created and shouldn't be removed) and must have an approved status. So I create this method :

<?php

namespace App\Entity;

use Knp\RadBundle\Doctrine\EntityRepository;

class ProductRepository extends EntityRepository
{

    public function findDisplayable()
    {
        return $this
            ->build()
            ->andWhere('p.createdAt <= :today')
            ->andWhere('p.removedAt > :today')
            ->andWhere('p.status = :status_approved')
            ->setParameter('status_approved', Product::STATUS_APPROVED)
            ->setParameter('today', new \DateTime)
            ->getQuery()
            ->getResult()
        ;
    }

}

But, I think is not clear anougth. If the entity grows and the application becomes more and more complex, this repository classe will has a lot of lines and will be unreadable.

My approche is to say : "One method, one state". For exemple, if I extract all states of my customer needs, I've got those states :

• created (or not)
• removed (or not)
• approved (or rejected)
• displayable

If you look at the GeekWeek12 repo, I've splitted my query building in separated methods but it's not clear anougth.

I propose to include custom query builder linked to the customs repository which containes states methods. For our example, it should be a class like this :

<?php

namespace App\Entity;

use Knp\RadBundle\Doctrine\QueryBuilder as BaseQueryBuilder;

use App\Entity\Product;

class ProductQueryBuilder extends BaseQueryBuilder
{

    public function whichAreCreated()
    {
        return $this
            ->andWhere(sprintf('%s.createdAt <= :today', $this->getAlias()))
            ->setParameter('today', new \DateTime)
        ;
    }

    public function whichAreNotCreated()
    {
        return $this
            ->andWhere(sprintf('%s.createdAt > :today', $this->getAlias()))
            ->setParameter('today', new \DateTime)
        ;
    }

    public function whichAreRemoved()
    {
        return $this
            ->andWhere(sprintf('%s.removedAt <= :today', $this->getAlias()))
            ->setParameter('today', new \DateTime)
        ;
    }

    public function whichAreNotRemoved()
    {
        return $this
            ->andWhere(sprintf('%s.removedAt > :today', $this->getAlias()))
            ->setParameter('today', new \DateTime)
        ;
    }

    public function whichAreApproved()
    {
        return $this
            ->andWhere(sprintf('%s.status = :status_approved', $this->getAlias()))
            ->setParameter('status_approved', Product::STATUS_APPROVED)
        ;
    }

    public function whichAreRejected()
    {
        return $this
            ->andWhere(sprintf('%s.status = :status_approved', $this->getAlias()))
            ->setParameter('status_approved', Product::STATUS_REJECTED)
        ;
    }

    public function whichAreDisplayable()
    {
        return $this
            ->whichAreCreated()
            ->whichAreNotRemoved()
            ->whichAreApproved()
        ;
    }

}

It's longer but it's clear and if the customer wants to redifined one of those states, you dont have to read all the repository to apply the modification.

What do you think?

[gquemener]

Could you give an example of how the product repository would use the rad query builder?

[PedroTroller]

I don't know if my method "whichAreDisplayable" are well placed... In fact, I don't know how the repository should interact with the QueryBuilder. My repository looks like this :

<?php

namespace App\Entity;

use Knp\RadBundle\Doctrine\EntityRepository;

class ProductRepository extends EntityRepository
{

        // replaced by DI
    public function build()
    {
        $qb = new ProductQueryBuilder($this->getEntityManager());

        return $qb
            ->setAlias($this->getAlias())
            ->select($this->getAlias())
            ->from($this->getEntityName(), $this->getAlias());
    }

    public function findAreDisplayable()
    {
        return $this
            ->build()
            ->whichAreDisplayable()
            ->getQuery()
            ->getResult()
        ;
    }

    public function findAreRemoved()
    {
        return $this
            ->build()
            ->whichAreRemoved()
            ->getQuery()
            ->getResult()
        ;
    }

}

If the customer defined a names entity state, i should create it in the QueryBuilder. But if it's juste a query sent to the DB, I write this query in the repository using one or more states of my QueryBuilder.

[leevigraham]

:thumbsup:

@Newism we've been working this way for the last project as well. Except for some reason we've been sending the QueryBuilder object into the repo to add the "filterBy" methods. Creating a entity specific QueryBuilder object is the way to go.

Have you ran into any other major issues?

[gquemener]

I think it's a good approach but it can already be achieved by extending the Knp\RadBundle\Doctrine\EntityRepository class.

[PedroTroller]

Yes, the result is the same but not the approch. You build a query in your repository but I propose to build the query in a QueryBuilder and so provide query templates to the repository and then write little or no DQL in the repository. And to push the concept further, it would also be possible to use the functions of a QueryBuilder in another and so filter a Join for example.