title: Many-to-Many with three tables relating with each other (SqlAlchemy)
body: <p>I've three tables User, Device and Role. I have created a many-to-many relation b/w User and Device like this;</p>
  
  <pre><code>#Many-to-Many relation between User and Devices
  userDevices = db.Table("user_devices",
                         db.Column("id", db.Integer, primary_key=True),
                         db.Column("user_id", db.Integer, db.ForeignKey("user.id")),
                         db.Column("device_id", db.Integer, db.ForeignKey("device.id"))))
  
  class User(db.Model):
      __tablename__ = 'user'
      id = db.Column(db.Integer, primary_key=True)
      username = db.Column(db.String(60), index=True, unique=True)
      devices = db.relationship("Device", secondary=userDevices, backref=db.backref('users'), lazy="dynamic")
  
  class Device(db.Model):
      __tablename__ = 'device'
      id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String(60), unique=True)
  </code></pre>
  
  <p>This works quiet well. I can assign a device <code>d1</code> to user <code>u1</code> > <code>d1.users.append(u1)</code>, and user to device > <code>u1.devices.append(d1)</code> and <code>db.session.commit()</code>.</p>
  
  <p>What I want more is to extend the table <code>user_devices</code> with one more column as <code>role_id</code> which will be ForeignKey for Role table. So that this table <code>user_devices</code> will clearly describe a <code>Role</code> for specific <code>User</code> on specific <code>Device</code>. after adding a column <code>role_id</code> in table <code>user_devices</code> I described <code>Role</code> table as;</p>
  
  <pre><code>class Role(db.Model):
      __tablename__ = 'role'
  
      id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String(60), unique=True)
      device = db.relationship("Device", secondary=userDevices, backref=db.backref('roles'), lazy="dynamic")
  </code></pre>
  
  <p>In this way, how can I assign a Role <code>r1</code> to User <code>u1</code> on Device <code>d1</code> ?
  here is what I tried:</p>
  
  <pre><code># First get the device, user and role 
  deviceRow = db.session.query(Device).filter(Device.name=="d1").first()
  userRow = db.session.query(User).filter(User.username=="u1").first()
  roleRow = db.session.query(Role).filter(Role.name == "r1").first()
  # Then add the user on that device
  deviceRow.users.append(userRow)
  deviceRow.roles.append(roleRow)
  </code></pre>
  
  <p>This creates two rows in the table <code>user_devices</code></p>
  
  <p><a href="https://i.stack.imgur.com/806aX.png" rel="noreferrer"><img src="https://i.stack.imgur.com/806aX.png" alt="enter image description here"></a></p>
  
  <p>Is there any way that we could add two attributes into the table like this ?;</p>
  
  <pre><code>deviceRow.users.append(userRow).roles.append(roleRow)
  </code></pre>
  
  <p>so that it creates only one row after commit() ?</p>