Update many to many association table with derived field

Question

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.

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))

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 clientid which is the same as the clientid 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 contactid and catid columns.

Example:

            category = Category.query.get(catid).first()
            if (category is not None):
                contact.categories.append(category)

This generates the SQL:

INSERT INTO `CONTACT_GROUPING` (catid, contactid) VALUES (%s, %s)

when I really need it to be:

INSERT INTO `CONTACT_GROUPING` (catid, contactid, clientid) VALUES (%s, %s, %s)

How can I define the relationship between Contact and Category such that this derived field is also populated via the ORM?

Answer 1

I had some help from https://twitter.com/140am on this one. I needed to represent contactgrouping as a Model rather than a Table, so that I could use relationships and access fields directly.

class Category(db.Model):

    resource_fields = {
        'id': fields.Integer(attribute='catid'),
        'name': fields.String(attribute='catname')
    }

    __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))

    def __repr__(self):
        return '<Category %r>' % (self.catname)

class ContactGrouping(db.Model):

    resource_fields = {
        'id': fields.Integer(attribute='catid'),
        'name': fields.String(attribute='category.catname'),
    }

    __tablename__ = 'CONTACT_GROUPING'
    __table_args__ = (
        PrimaryKeyConstraint('catid', 'contactid', 'clientid'),
    )

    catid = db.Column(db.Integer, db.ForeignKey('CATEGORY.catid'))
    contactid = db.Column(db.Integer, db.ForeignKey('CONTACT.contactid'))
    clientid= db.Column(db.Integer, db.ForeignKey('CONTACT.clientid'))
    category = db.relationship('Category')

    def __repr__(self):
        return '<ContactGrouping %r %r>' % (self.catid, self.contactid)

class Contact(db.Model):

    resource_fields = {
        'id': fields.Integer(attribute='contactid'),
        'firstname': fields.String,
        'lastname': fields.String,
        'categories': fields.List(fields.Nested(ContactGrouping.resource_fields))
    }

    __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('ContactGrouping', backref='contact', lazy='dynamic', foreign_keys='ContactGrouping.contactid')

    def __repr__(self):
        return '<Contact %r %r>' % (self.firstname, self.lastname)

and using it in the view:

        category = Category.query.get(catid).first()
        contactgrouping = ContactGrouping(contactid=id, catid=category.catid, clientid = contact.clientid)
        if (contactgrouping is not None):
            contact.categories.append(contactgrouping)