Doctrine 2 SQL Filter and annotation in ZF3

Table of Content

In a certain period of time, a user should see information about one marketplace. It can be tedious to add small WHERE condition each time when you want to work with the marketplace.

Doctrine provides an elegant solution to never forget this condition in your queries.

This approach can be used with Doctrine SQL Filter and Annotation.

This way make your code more simple and readable and increase development speed.

In our example, we have a Marketplace model and a Rank related to the Marketplace one. In one moment of time, a user should see information about Rank related to one Marketplace.

/**
 * @PoolAware(fieldName="marketplace")
 * @ORM\Entity()
 * @ORM\Table(name="product_rank")
 */
class Rank
{
    //...

    /**
     * @var Marketplace
     * @ORM\ManyToOne(targetEntity="Marketplace")
     * @ORM\JoinColumn(name="marketplaceId", referencedColumnName="id", nullable=true)
     */
    private $marketplace;
}

The main idea is that any query on the product_rank table should add a WHERE marketplaceId = :marketplaceId condition.

1. Create custom annotation for our entities

namespace Stagem\ZfcPool\Model\Annotation;

use Doctrine\Common\Annotations\Annotation;

/**
 * @Annotation
 * @Target("CLASS")
 */
final class PoolAware
{
    public $fieldName;

    public function getFieldName()
    {
        return $this->fieldName;
    }
}

$fieldName must be public.

2. Use the Annotation on Rank model

use Doctrine\ORM\Mapping as ORM;
use Stagem\ZfcPool\Model\Annotation\PoolAware;

/**
 * @PoolAware(fieldName="marketplace")
 * @ORM\Entity()
 */
class Rank { ... }

This is where all the magic goes: by only marking the entity this way, all queries on it (and even when the entity is used in joined queries) will add the WHERE condition.

3. Create Doctrine SQL Filter class

namespace Stagem\ZfcPool\Model\Filter;

use Doctrine\ORM\Query\SqlWalker;
use Doctrine\ORM\Mapping\ClassMetadata;
use Doctrine\ORM\Query\Filter\SQLFilter;
use Doctrine\Common\Annotations\AnnotationReader;
use Doctrine\ORM\Persisters\Entity\BasicEntityPersister;
use Stagem\ZfcPool\Model\Annotation\PoolAware;
use Stagem\ZfcPool\Service\PoolService;
use Stagem\ZfcPool\PoolHelper;

class PoolFilter extends SQLFilter
{
    /**
     * @var AnnotationReader
     */
    protected $reader;

    /**
     * @var PoolHelper
     */
    protected $poolHelper;

    public function setPoolHelper(PoolHelper $poolHelper)
    {
        $this->poolHelper = $poolHelper;
    }

    public function getPoolHelper()
    {
        return $this->poolHelper;
    }

    public function setAnnotationReader($reader)
    {
        $this->reader = $reader;
    }

    /**
     * Gets the SQL query part to add to a query.
     *
     * @param ClassMetaData $targetEntity
     * @param string $targetTableAlias
     *
     * @return string The constraint SQL if there is available, empty string otherwise.
     */
    public function addFilterConstraint(ClassMetadata $targetEntity, $targetTableAlias)
    {
        if (empty($this->reader)) {
            return '';
        }

        // The Doctrine filter is called for any query on any entity
        // Check if the current entity is "pool aware" (marked with an annotation)
        $poolAware = $this->reader->getClassAnnotation(
            $targetEntity->getReflectionClass(),
            PoolAware::class
        );
        if (!$poolAware) {
            return '';
        }

        if (!$poolHelper = $this->getPoolHelper()) {
            return '';
        }

        $fieldName = $poolAware->getFieldName();
        $pool = $poolHelper->current();
        if (empty($fieldName) || ($pool->getId() === PoolService::POOL_ADMIN)) {
            return '';
        }

        if (!$sqlWalker = $this->getSqlWalker()) {
            return '';
        }

        if (!isset($targetEntity->getAssociationMappings()[$fieldName])) {
            return '';
        }

        $mapping = $targetEntity->getAssociationMappings()[$fieldName];

        if (isset($mapping['joinColumns'])) {
            // oneToMany relation detected
            $table = $targetEntity->getTableName();
            $columnName = $mapping['joinColumns'][0]['name'];
            $dqlAlias = constant($targetEntity->getName() . '::MNEMO');
        } elseif (isset($mapping['joinTable'])) {
            // manyToMany relation detected
            $dqlAlias = constant($mapping['targetEntity'] . '::MNEMO');
            $component = $sqlWalker->getQueryComponent($dqlAlias);

            // Only main entity in query is interesting for us,
            // otherwise do not apply any filter
            if ($component['parent']) {
                return '';
            }
            $table = $mapping['joinTable']['name'];
            $columnName = $mapping['joinTable']['inverseJoinColumns'][0]['name'];
        } else {
            return '';
        }

        $tableAlias = ($sqlWalker instanceof BasicEntityPersister)
            ? $targetTableAlias // $repository->getBy() has been called
            : $sqlWalker->getSQLTableAlias($table, $dqlAlias);

        $query = sprintf('%s.%s = %s', $tableAlias, $columnName, $this->getConnection()->quote($pool->getId()));

        return $query;
    }

    /**
     * Get SqlWalker with debug_backtrace
     *
     * @return null|SqlWalker
     */
    protected function getSqlWalker()
    {
        $caller = debug_backtrace();
        $caller = $caller[2];

        if (isset($caller['object'])) {
            return $caller['object'];
        }

        return null;
    }
}

This Filter is universal and works with ManyToMany relation too. I do not find a more elegant solution except use debug_backtrace to get SqlWalker from which we get real table alias.

4. Configure Doctrine filter

Enable filter in zfc-pool/config/module.config.php

return [
    'doctrine' => [
        'configuration' => [
            'orm_default' => [
                'filters' => [
                    'pool_filter' => \Stagem\ZfcPool\Model\Filter\PoolFilter::class,
                ],
            ],
        ],
    ],
];

Attach listener on Zend\Mvc\MvcEvent::EVENT_DISPATCH for initialize filter object.

namespace Stagem\ZfcPool;

use Doctrine\ORM\EntityManager;
use Doctrine\Common\Annotations\AnnotationReader;
use Zend\Mvc\Controller\AbstractController;
use Zend\Mvc\MvcEvent;
use Stagem\ZfcPool\Model\Filter\PoolFilter;

class Module
{
    //...

    /**
     * @param MvcEvent $e
     */
    public function onBootstrap(MvcEvent $e)
    {
        $app = $e->getApplication();
        $container = $app->getServiceManager();
        $eventManager = $app->getEventManager();
        $sharedEvents = $eventManager->getSharedManager();

        $sharedEvents->attach(AbstractController::class, MvcEvent::EVENT_DISPATCH, function(MvcEvent $mvcEvent) use ($container) {
            /** @var PoolHelper $poolHelper */
            $om = $container->get(EntityManager::class);
            $poolHelper = $container->get(PoolHelper::class);
            $reader = $container->get(AnnotationReader::class);

            /** @var PoolFilter $filter */
            $filter = $om->getFilters()->enable('pool_filter');
            $filter->setAnnotationReader($reader);
            $filter->setPoolHelper($poolHelper);
        }, 1000);
    }
}

5. All done!

From now on, any query involving the Rank entity will have a marketplaceId = :marketplaceId condition, be it a select, an inner join and so on.

For any other entity that should be restricted to the selected marketplace, just add the @PoolAware annotation on it and all queries will automatically append with a WHERE condition.

All of the following queries will automatically add the WHERE condition (let’s suppose that the selected marketplace had the 5 id):

// SELECT * FROM product_rank WHERE id = 1 AND user_id = 5
$this->em->getRepository('Rank')->find(1);

With full ZfcPool module you can get acquainted on github

6. What are the drawbacks of using Filters?

Arguably the benefit of using filters could also be a drawback.

Business rules should be articulated within the domain of your code, and not your ORM. The ORM is essentially “replaceable”, and so it would probably be a bad practice to encode business critical rules at that level of abstraction.

If that is the case for your project, you might be better off using filters as a way of filtering out certain “bad” data results or deleted entities. Basically as a way of filtering data, but not as a solution to implementing your business rules.

More links
Doctrine filters and annotations to improve security and ease development in a Symfony app
Go Pro with Doctrine Queries Tutorial
Working with Doctrine 2 Filters
Applying doctrine sql filter in many to many relationships

Leave a Reply

Your email address will not be published. Required fields are marked *