How to handle additional columns in join tables wh

Question

Let's assume I have two Entities in my Symfony2 bundle, User and Group. Associated by a many-to-many relationship.

    ┌────────────────┐         ┌────────────────┐         ┌────────────────┐
    |      USER      |         | USER_GROUP_REL |         |     GROUP      |
    ├────────────────┤         ├────────────────┤         ├────────────────┤
    | id#            ├---------┤ user_id#       |    ┌----┤ id#            |
    | username       |         | group_id#      ├----┘    | groupname      |
    | email          |         | created_date   |         |                |
    └────────────────┘         └────────────────┘         └────────────────┘

What would be a good practice or a good approach to add additional columns to the join table, like a created date which represents the date when User joined Group?

I know that I could use the QueryBuilder to write an INSERT statement.

But as far as I have not seen any INSERT example of QueryBuilder or native SQL which makes me believe that ORM/Doctrine try to avoid direct INSERT statements (e.g. for security reasons). Plus as far as I have understood Symfony and Doctrine I would be taken aback if such a common requirement wouldn't be covered by the framework.

Answer 1

You want to set a property of the relation. This is how it's done in doctrine: doctrine 2 many to many (Products - Categories)

I answered that question with a use case (like yours).

This is an additional question / answer which considers the benefits and use cases: Doctrine 2 : Best way to manage many-to-many associations