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