Propper entity associations mapping with one entity for join table (Doctrine 2)

Question

I have three tables in my MySQL database which looks like theese:

CREATE TABLE `role` (
  `id`    int(10),
  `name`  varchar(50),
  `order` tinyint(3)
);


CREATE TABLE `user` (
  `id`       int(10),
  `username` varchar(50),
  `password` char(60)
);


CREATE TABLE `user_role` (
  `id`      int(10),
  `user_id` int(10),
  `role_id` int(10),
  `order`   int(10),
  KEY `user_id` (`user_id`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `user_role_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
  CONSTRAINT `user_role_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
);

NOTE: I deleted some definition parts from those tables like AUTO_INCREMENT and so just to keep the schema simpler.

My problem is, that I need to map these tables to entity classes, like User and Role but I don´t know how to manage the order column in user_role table and how to manage these relations in entities.

My first guess was:

File User.php

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * User entity.
 *
 * @ORM\Entity
 * @ORM\Table(name="user")
 */
class User
{

    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue
     *
     * @var integer
     */
    protected $id;

    /**
     * @ORM\ManyToMany(targetEntity="Role", inversedBy="users")
     * @ORM\JoinTable(name="user_role")
     *
     * @var Role[]
     */
    protected $roles;

    /**
     * @ORM\Column(type="string")
     *
     * @var string
     */
    protected $username;

    /**
     * @ORM\Column(type="string")
     *
     * @var string
     */
    protected $password;


    public function __construct()
    {
        $this->roles = new ArrayCollection;
    }

}

File Role.php

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * Role entity.
 *
 * @ORM\Entity
 * @ORM\Table(name="role")
 */
class Role
{

    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue
     *
     * @var integer
     */
    protected $id;

    /**
     * @ORM\ManyToMany(targetEntity="User", mappedBy="roles")
     *
     * @var User[]
     */
    protected $users;

    /**
     * @ORM\Column(type="string")
     *
     * @var string
     */
    protected $name;


    public function __construct()
    {
        $this->users = new ArrayCollection;
    }

}

These entities will generate three tables like I need, but there is no order column in the user_role table which I really need. It´s important to have it there. I also could create third entity called for example UserRole but I don´t know what would be the mapping information then like.

Any help please?

Answer 1

The easiest way to archieve managing the parameters within ManyToMany relation is creating the SomethingAndSomething entity class in your case UserAndRole, here's a quick example that should give you an idea how to archieve what you want

/** @Table(name="user_role") */
class UserAndRole
{
    /** @Column */
    private $id;

    // get

    /** @ManyToOne(targetEntity="Acme\Entity\User", inversedBy="userAndRoles") */ 
    private $user;

    // get / set

    /** @ManyToOne(targetEntity="Acme\Entity\Role") */ 
    private $role;

    // get / set

    /** @Column */
    private $order;

    // get / set
}

And here is your user class

/** @Table(name="user") */
class User
{

    /** @OneToMany(targetEntity="Acme\Entity\UserAndRole", mappedBy="user") */
    private $userAndRoles;

    // add / remove / get / set
}

And in your controller / event etc you just do

$userAndRole = new UserAndRole();
$userAndRole->setUser($user);
$userAndRole->setRole($role);
$userAndRole->setOrder($order);
// PERSIST

Answer 2

You have applied here manyTomany relationship so user_role is relationship table .so it will generate only relation columns .you can add new column manualy in your table but it will not filled when you will create a role for user it will not filled the order column .

you shoul create new entity userRoleOrder with following properties userid,roleid,and order use oneToOne relation between user entity and userRoleOrder entity