Multiple JoinColumns in Symfony2 using Doctrine an

Question

Here is the problem:

Class Routing with attributes objectId and objectType. objectId is an int, and objectType is a string. The reason for this was to allow the same table to hold data for different kind of routings. For instance for the routing of Products, Department and Brand. So, the combination of the objectType and the objectId is my JoinColumn.

How do I create such a bidirectional relationship with Doctrine2? I looked at inherited relationships, but none of the concepts seemed to be what I'm looking for.

I could create some views in the database and just have a couple of different Routing entities, but this does not seem the best route.

Here are my entities Department, Product and Brand.

../Entity/Department.php

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity()
 * @ORM\Table(name="departments")
 */
class Department implements DescribableInterface
{
    /**
     * @ORM\Id
     * @ORM\Column(name="id", type="integer", length=11)
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(name="status", type="string", length=1)
     */
    private $status;

    /**
     * @ORM\Column(name="name", type="string", length=255)
     */
    private $name;
...

../Entity/Product.php

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="products")
 */
class Product implements DescribableInterface
{
    /**
     * @ORM\Id
     * @ORM\Column(name="id", type="integer", length=11)
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(name="status", type="string", length=1)
     */
    private $status;

    /**
     * @ORM\Column(name="product_code", type="string", length=100, nullable=true)
     */
    private $productCode = '';

    /**
     * @ORM\Column(name="name", type="string", length=255)
     */
    private $name;
...

../Entity/Brand.php

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="brands")
 */
class Brand
{
    /**
     * @ORM\Id
     * @ORM\Column(name="id", type="integer", length=11)
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(name="status", type="string", length=1)
     */
    private $status = 'a';

    /**
     * @ORM\Column(name="name", type="string", length=255)
     */
    private $name;
...

Each product, brand and department has its own URL that is held in the routing table set by the object_type and object_id where the object_type is simply department, product or brand and the object_id is the unique id of the corresponding product, brand or department.

../Entity/Routing.php

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="routing")
 */
class Routing
{
    /**
     * @ORM\Id
     * @ORM\Column(name="id", type="integer", length=11)
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;    

    /**
     * @ORM\Column(name="object_id", type="integer", length=11)
     */
    private $objectId;

    /**
     * @ORM\Column(name="object_type", type="string", length=100)
     */
    private $objectType;

    /**
     * @ORM\Column(name="url", type="text")
     */
    private $url;
...

What I am really struggling with is how do I setup the relationship, so the departments, products and brands can access their URL from the single routing entity.

I have tried adding the relationships to the $objectId, but it doesn't seem to like that. Is it possible to set this up like this?

What I am basically trying to achieve is to get the data object and have the ability to get the URL of an object, for example:

$departments = $em->getRepository("AdamStaceySiteBundle:Department")->findAll();
foreach ($departments as $department)
{
   echo '<a href="'.$department->getUrl().'">'.$department->getMenuTitle().'</a>;
}

Can anyone help?

Answer 1

After further researching I found a man (Dirk Olbertz) in the know who had the same problem.

Information can be found at: Google Groups: Multiple JoinColumns?

I have now implemented this and I will explain how I did it incase it might help anyone else.

The answer to my problem was the use of single table inheritance.

The first thing I needed to do was update the routing entity to use single table inheritance:

../Entity/Routing.php

/**
 * @ORM\Entity
 * @ORM\InheritanceType("SINGLE_TABLE")
 * @ORM\DiscriminatorColumn(name="object_type", type="string")
 * @ORM\DiscriminatorMap({"product" = "ProductRouting", "department" = "DepartmentRouting", "brand" = "BrandRouting"})
 * @ORM\Table(name="routing")
 */
class Routing
{
    /**
     * @ORM\Id
     * @ORM\Column(name="id", type="integer", length=11)
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;
...

The DiscriminatorColumn allowed me to specify what column would be used to link, which in this case was the object_type field.

The DiscriminatorMap allowed me to specify what object_type will link with what entities.

These entities then had to be created that extended the Routing entity.

../Entity/ProductRouting.php

/**
 * @ORM\Entity
 */
class ProductRouting extends Routing
{
    /**
     * @ORM\ManyToOne(targetEntity="Product")
     * @ORM\JoinColumn(name="object_id", referencedColumnName="id")
     */
    protected $product;
...

../Entity/DepartmentRouting.php

/**
 * @ORM\Entity
 */
class DepartmentRouting extends Routing
{
    /**
     * @ORM\ManyToOne(targetEntity="Department")
     * @ORM\JoinColumn(name="object_id", referencedColumnName="id")
     */
    protected $department;
...

../Entity/BrandRouting.php

/**
 * @ORM\Entity
 */
class BrandRouting extends Routing
{
    /**
     * @ORM\ManyToOne(targetEntity="Brand")
     * @ORM\JoinColumn(name="object_id", referencedColumnName="id")
     */
    protected $brand;
...

Then in each of the Product, Department and Brand entities I needed to add the new $routings.

../Entity/Product.php

...
class Product
{
    ...
    /**
     * @ORM\OneToMany(targetEntity="ProductRouting", mappedBy="product", cascade={"all"})
     */
    private $routings;
...

../Entity/Department.php

...
class Department
{
    ...
    /**
     * @ORM\OneToMany(targetEntity="DepartmentRouting", mappedBy="department", cascade={"all"})
     */
    private $routings;
...

../Entity/Brand.php

...
class Brand
{
    ...
    /**
     * @ORM\OneToMany(targetEntity="BrandRouting", mappedBy="brand", cascade={"all"})
     */
    private $routings;
...

Hope that helps...