Querying Entity Relation Tables In OROCRM & ORO Platform
Entity Relation Tables
If you have worked with the OROCRM or ORO Platform, then you are likely familiar with extended entities. If you are not familiar, extended entities are any entity, say a Task or Contact record where you want to add additional custom fields for your application. These extended entity files and any relations that you have added via schema migrations are stored in the app/cache/prod/oro_entities and the app/cache/dev/oro_entities directories.
In your schema migrations, if you do not explicitly set what you want the relation table name to be, Oro generates one consisting of a prefix and a random string of numbers. You can see this done in the Oro\Bundle\EntityExtendBundle\Tools\ExtendDbIdentifierNameGenerator class and calls the generateManyToManyJoinTableName function. The table name would end up being named something like oro_rel_1a32sdf45as3d1f3as51d.
Doctrine Limitations
Let’s say that you have a Task entity that is related to a custom entity you created via a many to one relation. For an example, let’s say your custom entity is a Vendor. Assuming you created your schema migration the ORO way, the Task entity will now have an extended entity file in the cache directories I talked about earlier. That file, is the owning side of the relation and will have a property with something like vendor_be56a0 that is the index to the Vendor entity relation. The random string on the property is also populated by an ORO service. The file will have get and set methods with the relation to the Vendor like any other entity file.
I know what you’re asking, if there is a relation, then why not just use the regular Doctrine query like $task->getVendor(). The first thing is that the getter wouldn’t be getVendor(), it would be getVendorBe56a0() instead. You could look in the extended entity file and get that property and hard code it, but it would be safer and more reliable to use the ORO service that I will talk about below. The next problem is if you want to query every Task that has a relation to the one Vendor entity. Typically you would write a query using the Doctrine createQueryBuilder or createQuery methods. This won’t work, you can try if you don’t believe me. Instead you have to directly query the relation table to find all rows where the vendor_id matches the parameter passed that you are looking for.
The reason you have to directly query the entity relation table, is because Doctrine lacks the capability to build the query in some cases. Why? Doctrine (DQL) only works with entities, and the relation tables are not classes or entities that are managed by Doctrine. It is also much more efficient to directly query the database instead when working with entity relation tables.
Getting Your Class Ready
I am going to assume that you already know how to inject dependencies into your class via a services.yml file or if you instantiate your class with the new keyword. There are only two services that you will need in your class. The Oro\Bundle\EntityExtendBundle\Tools\ExtendHelper, and Oro\Bundle\EntityBundle\ORM\DoctrineHelper classes.
Let’s Break It Down
Step One: Get Association Name
In the scenario above the first step to querying the database to get the relation table name is to use the ExtendHelper class. This class has a public static function buildAssociationName that takes two parameters. The fully qualified class name, and the relation kind which defaults to null if nothing is passed. In our case you would want to pass 'activity' because the Task entity implements the Oro\Bundle\ActivityBundle\Model\ActivityInterface. This is the method I mentioned I would get to earlier, that should be used instead of hard coding getVendorBe56a0().
$associationName = ExtendHelper::buildAssociationName(get_class($class), 'activity');Step Two: Get Metadata For Task Entities
The next step is using the DoctrineHelper class and calling the getEntityMetadataForClass method which requires the class name for the entity you want the data from and an optional argument to throw an exception that defaults to true. This gives you an instance of the Doctrine\ORM\Mapping\ClassMetadataInfo class.
$entityMetadata = $this->doctrineHelper->getEntityMetadata('OroTaskBundle:Task');Step Three: Get Association Mapping Information
The third step is to get the association mapping, which is, you guessed it, is called getAssociationMapping and you pass the association name that you retrieved from step one. This returns a multidimensional array of information for the mapping that includes, wait for it, the relation table name!
$association = $entityMetadata->getAssociationMapping($associationName);Step Four: Retrieve Table Name
Step four is very easy, set a variable that will hold the table name, which is retrieved from the $association variable.
$tableName = $association['joinTable']['name'];Step Five: Get Default Database Connection Object
To get the core Doctrine connection to the database, Doctrine\DBAL\Connection, you can use the DoctrineHelper class to call the getEntityManagerForClass() method, and then call the getConnection method.
$this->doctrineHelper->getEntityManagerForClass('OroTaskBundle:Task')->getConnection();Step Six: Write A Statement
Next you can write the SQL statement.
$dql = "SELECT * FROM $tableName WHERE vendor_id = ?";Step Seven: Execute Statement & Fetch Results
Finally, you simply need to execute your statement and fetch the results.
$stmt = $connection->prepare($dql);
$stmt->bindValue(1, $class->getId());
$stmt->execute();
$relations = $stmt->fetchAll();
The last two steps are not just for OROCRM or ORO Platform, it is native to Symfony or any application that is using Doctrine for the ORM.
The full code output is below so that you can see it all in one easy to copy and paste block.
$associationName = ExtendHelper::buildAssociationName(get_class($class), 'activity');
$entityMetadata = $this->doctrineHelper->getEntityMetadata('OroNoteBundle:Note');
$association = $entityMetadata->getAssociationMapping($associationName);
$tableName = $association['joinTable']['name'];
$connection = $this->om->getConnection();
$dql = "SELECT * FROM $tableName WHERE business_id = ?";
$stmt = $connection->prepare($dql);
$stmt->bindValue(1, $class->getId());
$stmt->execute();
$relations = $stmt->fetchAll();
I hope you can use this! Let me know if you did or if you know another way to get a relation table name with a comment below!
No Comments