Last month I was participating in developing solution which provides reporting features using Symfony that deals with data partitioned over multiple MySQL servers. Data was sharded among two reporting databases that have identical structure, each database store trading history for a separate company branch/trading system.
So basically, there is a bundle that deals with multiple connections – thus different entity managers- dynamically. Handling those connections in Symfony is straight forward, but for sonata admin bundle, it needed some work to get it work.
Project Structure
The project contains bundle called WebitReportingBundle
which have the entities related to reporting database, I mainly use entity called MT4Trades
for this post (it holds trades information performed by clients)
This bundle uses two entity managers, depending on the application context, here is portion of config.yml
containing entity managers/connection mappings:
orm: default_entity_manager: default entity_managers: default: connection: default mappings: FOSUserBundle: ~ ApplicationSonataUserBundle: ~ SonataUserBundle: ~ AppBundle: ~ reporting_real: connection: reporting_real mappings: WebitReportingBundle: ~ reporting_real2: connection: reporting_real2 mappings: WebitReportingBundle: ~
In the config file above, there are two extra entity managers (
real_reporting
& real_reporting2
), each handles connection to separate database.
In Symfony, the entity manager can be specified with doctrine repository simply as following:
<?php namespace Webit\ReportingBundle\Controller; use Symfony\Bundle\FrameworkBundle\Controller\Controller; class DefaultController extends Controller { public function IndexAction(Request $request) { //retrieve trades from server 1 $obj_server1 = $this->getDoctrine() ->getRepository('WebitReportingBundle:MT4Trades', 'reporting_real') ->findOneBy(['ticket'=>42342]); //retrieve trades from server 2 $obj_server1 = $this->getDoctrine() ->getRepository('WebitReportingBundle:MT4Trades', 'reporting_real2') ->findOneBy(['ticket'=>123123]); } }
but when I wanted to do the same on sonata admin module, there was no easy way to handle that.
How Sonata Admin Bundle handle multiple connections
When you create sonata admin file for certain entity with “SonataDoctrineORMAdminBundle
“, “Admin” class will determine the entity manager to use by calling “getManagerForClass()
” method in the associated “ModelManager
” object.
As in the following code, which is taken from ModelManager class inside sonataDoctrineORMAdminBundle:
<?php namespace Sonata\DoctrineORMAdminBundle\Model; class ModelManager implements ModelManagerInterface, LockInterface { public function getEntityManager($class) { if (is_object($class)) { $class = get_class($class); } if (!isset($this->cache[$class])) { //detect entity manager based on class name automatically $em = $this->registry->getManagerForClass($class); if (!$em) { throw new \RuntimeException(sprintf('No entity manager defined for class %s', $class)); } $this->cache[$class] = $em; } return $this->cache[$class]; } }
However, this method will be an issue for entities when dealing with sharded databases, so in my case, the first matching entity manager will be always selected – according to mappings defined in config.yml – and there is no direct way to specify the preferred entity manager for the admin class.
Solution
In order to work around this problem, I performed the following:
- Define custom model manager extending the
ModelManager
class, and override methods “getEntityManager
” and “createQuery
” to use specific entity manager if it is defined in the class, as following:
<?php namespace Webit\ReportingBundle\Model; use Sonata\DoctrineORMAdminBundle\Model\ModelManager as BaseModelManager; use Sonata\DoctrineORMAdminBundle\Datagrid\ProxyQuery; class CustomModelManager extends BaseModelManager { /* @var $emName stores the preferred entity manager name */ protected $emName; /** * set preferred entity manager name to be used in ModelManager * @param string $name */ public function setEntityManagerName($name){ $this->emName = $name; } /** * {@inheritdoc} */ public function createQuery($class, $alias = 'o') { //adding second parameter to getRepository method specifying entity manager name $repository = $this->getEntityManager($class) ->getRepository($class,$this->emName); return new ProxyQuery($repository->createQueryBuilder($alias)); } /** * {@inheritdoc} */ public function getEntityManager($class) { if (is_object($class)) { $class = get_class($class); } if (isset($this->cache[$class]) === false) { //return fixed value if preferred entity manager name specified if (isset($this->emName) === true) { $this->cache[$class] = $this->registry->getEntityManager($this->emName); } else { $this->cache[$class] = parent::getEntityManager($class); } } return $this->cache[$class]; } }
- Add new model manager as a service and associate it with the admin service entries of the entity:
services: reporting.model_manager: class: Webit\ReportingBundle\Model\CustomModelManager arguments: - '@doctrine' reporting.admin.trades: class: Webit\ReportingBundle\Admin\MT4TradesAdmin tags: - { name: sonata.admin, manager_type: orm, group: webit.admin.group.reporting, label: "Trades (server 1)", pager_type: "simple" } arguments: [null,Webit\ReportingBundle\Entity\MT4Trades, WebitReportingBundle:Admin\MT4Trades] calls: - [setModelManager, ['@reporting.model_manager'] ] reporting.admin.trades2: class: Webit\ReportingBundle\Admin\MT4Trades2Admin tags: - { name: sonata.admin, manager_type: orm, group: webit.admin.group.reporting, label: "Trades (server 2)", pager_type: "simple" } arguments: [null,Webit\ReportingBundle\Entity\MT4Trades, WebitReportingBundle:Admin\MT4Trades] calls: - [setModelManager, ['@reporting.model_manager'] ]
Here I am having two admins for the same entity, each admin shall use different connection. - Define the entity manager name inside the admin classes by overriding
setModelManager
method, as following:
<?php namespace Webit\ReportingBundle\Admin; use Sonata\AdminBundle\Admin\Admin; /** * admin for MT4Trades (Server 1) */ class MT4TradesAdmin extends Admin{ protected $emName = 'reporting_real'; protected $baseRouteName = 'admin_webit_reporting_mt4trades'; protected $baseRoutePattern = 'admin-reporting-mt4trades-server'; public function setModelManager(\Sonata\AdminBundle\Model\ModelManagerInterface $modelManager) { parent::setModelManager($modelManager); //override setModelManager to specify the preferred entity manager for this admin class $modelManager->setEntityManagerName($this->emName); } //remaining admin class methods... }
and here the other admin class that is connecting to the second database:
<?php namespace Webit\ReportingBundle\Admin; /** * admin for MT4Trades (Server 2) */ class MT4Trades2Admin extends MT4TradesAdmin{ protected $emName = 'reporting_real2'; //second entity manager will be used //specify route name and pattern to get two admin classes work with same entity protected $baseRouteName = 'admin_webit_reporting_mt4trades2'; protected $baseRoutePattern = 'admin-reporting-mt4trades-server2'; //... }
I extended second admin class from the first, so they have identical configurations/logic, with different connection used.
Conclusion
Now, I have two identical admin classes for the same entity, each using different entity manager correctly. and if additional server is deployed in the future, I just have to define new admin classes -along with new connection/entity manager – with placing the correct $emName
value.
Got this error… A new entity was found through the relationship ‘Pequiven\SEIPBundle\Entity\User#company’ that was not configured to cascade persist operations for entity: “emName”…. Any ideas?
seems something may be wrong with relation annotation; I assume you missed
cascade={"persist"}
,something like that:
* @ORM\OneToMany(targetEntity="ContentTranslation", mappedBy="translation_parent", cascade={"persist","remove"}, orphanRemoval=true)