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

1
votes

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?

2
phpentity-frameworkormdoctrine-orm
What is the order column intended to do? If you need it in your user_role table, you probably need it in the class, and should create the UserRole class. – Eric Hughes
The purpose of the order column in user_role table is to store the information what position the user is in given role. Probably the table should be renamed to something like user_in_role_order which seems to be semantically more correct but still there is an issue with the third UserInRoleOrder entity class. What should the mapping information looks like? Is it possible with the Role->users to have this property referencing the User directly and still have the resulting collection in Role->users sorted by UserInRoleOrder->order? How can I do this? I am still new to Doctrine 2. – user3683974

2 Answers

0
votes

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

0
votes

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