CODEWITHSUNDEEP
doctrine-ormmany-to-manyentity-relationship
Luke
Luke

Reputation: 21236

Doctrine ManyToMany relationship issues

I'm trying to add Doctrine entities and relationships to my existing database schema but I'm running into some issues.

I have 4 tables:

+-------------+  +-----------+  +-----------------+  +-------------------+
| customers   |  | acl_roles |  | acl_permissions |  | acl_customer_role |
---------------  -------------  -------------------  ---------------------
| customer_id |  | id        |  | role_id         |  | customer_id       |
+-------------+  | name      |  | resource_id     |  | acl_role_id       |
                 +------------  | flags           |  +--------------------
                                +------------------

In my ACL customers can have many roles and each role can have many permissions. The mapping of customers/roles is done through the acl_customer_role table.

I'm currently having issues making this relationship work. These are my entities (removed some standard annotations for brevity):

class Customer {

   /**
    * @ORM\ManyToMany(targetEntity="AclRole", cascade="persist")
    * @ORM\JoinTable(name="acl_customer_role",
    *    joinColumns={@ORM\JoinColumn(name="acl_role_id", referencedColumnName="customer_id")}
    * )
    */
   protected $roles;

}

class AclRole {

    /**
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    protected $id;

}

As you can see, in my customer entity I'm defining the $roles. It's a ManyToMany relationship since many roles can belong to many customers. I'm setting up my join table to be acl_customer_role and I specify the columns on which the join needs to take place. However, I get the following error:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'acl_customer_role.aclrole_id' in 'on clause'' in vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:641

It seems that Doctrine's query is trying to join on 'acl_customer_role.aclrole_id' which obviously doesn't exists.

How can I specify the relationship correctly?

UPDATE:

Somehow it seems that Doctrine is modifying my column name. When I specify acl_role_id Doctrine strips out the first underscore and assumes that the column name is aclrole_id (as demonstrated in the error message in the question above). However, when I add two underscores such as acl__role_id it leaves all underscores in there and gives pretty much the same error other than that now it can't join on acl__role_id.

I'm pretty much at a loss..

Upvotes: 0

Views: 568

Answers (1)

Kamil Adryjanek
Kamil Adryjanek

Reputation: 3338

I know this question is old but recently i have came across the same error / problem and i have found the solution.

By default Doctrine is using DefaultNamingStrategy class for generating e.g. joinColumn, joinTableName, propertyToColumnName names:

...
public function joinColumnName($propertyName)
{
    return $propertyName . '_' . $this->referenceColumnName();
}

That's why in your case aclrole_id column was generated.

To change that behavior all you have to do is to change naming strategy for Doctrine2 to underscore in you app/config.yml file:

doctrine:

    orm:
        # ...
        naming_strategy: doctrine.orm.naming_strategy.underscore

UnderscoreNamingStrategy class

...
public function joinColumnName($propertyName)
{
    return $this->underscore($propertyName) . '_' . $this->referenceColumnName();
}

This would generate: acl_role_id

You can also implement your custom naming strategy: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/namingstrategy.html

Note: This was introduced in version 2.3.

Upvotes: 2

Related Questions