doctrine orm - Symfony: ManyToMany table extra columns

Question

I have a many to many table for User and House, called user_house. Instead of just two columns: user_id and house_id, i want to add 3 more: eg action, created_at, updated_at. How can I do this?

I cannot find any relevant docs on this.

The following just creates a separate table with two columns in it.

class User extends EntityBase
{
    ...
    /**
     * @ORMManyToMany(targetEntity="AppBundleEntityHouse")
     */
    protected $action;

Basically, what I want to achieve is:

in the user_house table the combination of user_id, house_id, action should be unique.

when a user clicks a "view" on a house, user_house table gets updated with some user_id, some house_id, view, now(), now()

when a user clicks a "like" on a house, user_house table gets updated with some user_id, some house_id, like, now(), now()

when a user clicks a "request a call" on a house, user_house table gets updated with some user_id, some house_id, contact, now(), now()

Could someone point me in the right direction? Thanks!

Answer 1

You need to break your ManyToMany relation to OneToMany and ManyToOne by introducing a junction entity called as UserHasHouses, This way you could add multiple columns to your junction table user_house

User Entity

/**
 * User
 * @ORMTable(name="user")
 * @ORMEntity
 */
class User
{
    /**
     * @ORMOneToMany(targetEntity="NameSpaceYourBundleEntityUserHasHouses", mappedBy="users",cascade={"persist","remove"} )
     */
    protected $hasHouses;

}

House Entity

/**
 * Group
 * @ORMTable(name="house")
 * @ORMEntity
 */
class House
{
    /**
     * @ORMOneToMany(targetEntity="NameSpaceYourBundleEntityUserHasHouses", mappedBy="houses",cascade={"persist","remove"} )
     */
    protected $hasUsers;

}

UserHasHouses Entity

/**
 * UserHasHouses 
 * @ORMTable(name="user_house")
 * @ORMEntity
 */
class UserHasHouses 
{

    /**
     * @var integer
     *
     * @ORMColumn(name="id", type="integer")
     * @ORMId
     * @ORMGeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORMManyToOne(targetEntity="NameSpaceYourBundleEntityHouse", cascade={"persist"}, fetch="LAZY")
     * @ORMJoinColumn(name="house_id", referencedColumnName="id")
     */
    protected $houses;

    /**
     * @ORMManyToOne(targetEntity="NameSpaceYourBundleEntityUser", cascade={"persist","remove"}, fetch="LAZY" )
     * @ORMJoinColumn(name="user_id", referencedColumnName="id",nullable=true)
     */
    protected $users;


    /**
     * @var DateTime
     * @ORMColumn(name="created_at", type="datetime")
     */
    protected $createdAt;


    /**
     * @var DateTime
     * @ORMColumn(name="updated_at", type="datetime")
     */
    protected $updatedAt;
     //... add other properties
    public function __construct()
    {
        $this->createdAt= new DateTime('now');
    }

}

have additional column in ManyToMany join table in Doctrine (Symfony2)