title: Update many to many association table with derived field
body: <p>I'm writing a RESTful API using Flask, Flask-SQLalchemy and Flask-Restful. I have models for Contact and Category, and a helper table for mapping a many-to-many relationship between them.</p>
  
  <pre><code>contactgrouping = db.Table('CONTACT_GROUPING',
      db.Column('catid', db.Integer, db.ForeignKey('CATEGORY.catid')),
      db.Column('contactid', db.Integer, db.ForeignKey('CONTACT.contactid')),
      db.Column('clientid', db.Integer, db.ForeignKey('CLIENT.clientid')),
  )
  
  class Contact(db.Model):
  
      __tablename__ = 'CONTACT'
      contactid = db.Column(db.Integer, primary_key=True)
      clientid = db.Column(db.Integer, db.ForeignKey('CLIENT.clientid'))
      firstname = db.Column(db.String(50))
      lastname = db.Column(db.String(50))
      categories = db.relationship('Category',secondary = contactgrouping, backref='contact', lazy='dynamic')
  
  class Category(db.Model):
  
      __tablename__ = 'CATEGORY'
      catid = db.Column(db.Integer, primary_key=True)
      clientid = db.Column(db.Integer, db.ForeignKey('CLIENT.clientid'))
      catname = db.Column(db.String(50))
  </code></pre>
  
  <p>I am using the ORM to update the categories for a given contact, and this is working well. The complication is that the mapping table has a de-normalised field <code>clientid</code> which is the same as the <code>clientid</code> in the Contact table. I need this field to be populated when the ORM updates a category assignment for a contact, but it is only inserting the <code>contactid</code> and <code>catid</code> columns.</p>
  
  <p>Example:</p>
  
  <pre><code>            category = Category.query.get(catid).first()
              if (category is not None):
                  contact.categories.append(category)
  </code></pre>
  
  <p>This generates the SQL:</p>
  
  <pre><code>INSERT INTO `CONTACT_GROUPING` (catid, contactid) VALUES (%s, %s)
  </code></pre>
  
  <p>when I really need it to be:</p>
  
  <pre><code>INSERT INTO `CONTACT_GROUPING` (catid, contactid, clientid) VALUES (%s, %s, %s)
  </code></pre>
  
  <p>How can I define the relationship between Contact and Category such that this derived field is also populated via the ORM?</p>