Update a sqlalchemy association proxy target when updating the parent ORM table

Question

I have a Flask SQLAlchemy project with 3 ORM tables, Category, Vendor, and CategoryVendorMap, and I am using Flask-Marshmallow to define schemas for the api.

Relationships: Category has many Vendors by way of CategoryVendorMap. CategoryVendorMap also has an is_default flag indicating the default Vendor for a Category. This is_default field has also been denormalized onto the Category table.

Is there a way to set the is_default value of the CategoryVendorMap object by only accessing the Category ORM Mapper? Basically I want to set the Category.default_vendor_id field and have the setter for this field updated the appropriate CategoryVendorMap associations.I know I could just update the CategoryVendorMap objects directly, however ultimately my models are exposed via an api and I would like to avoid exposing this object, and creating custom controller logic to handle this use case.

class CategoryVendorMap(db.Model):
    __tablename__ = "CategoryVendorMap"
    __table_args__ = (
      db.PrimaryKeyConstraint('category_id', 'vendor_id'),
    )
    category_id = db.Column(db.Integer, db.ForeignKey('Category.category_id'))
    vendor_id = db.Column(db.Integer, db.ForeignKey('Vendor.vendor_id'))
    is_default = db.Column(db.Boolean, default=False)

    category = db.relationship('Category', backref=db.backref("category_vendors", cascade="all, delete-orphan"))
    vendor = db.relationship('Vendor')


class Category(db.Model):
    __tablename__ = 'Category'

    @property    
    def default_vendor_id(self):
        if self.category_vendors:
            default_vendor_id = [cat_vendor_map for cat_vendor_map in self.category_vendors if cat_vendor_map.is_default]
            return default_vendor_id[0].vendor_id if default_vendor_id else None
        return

    @default_vendor_id.setter
    def default_vendor_id(self, vendor_id):
      return int(vendor_id)

    category_id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), unique=False, nullable=False)    

    vendors = association_proxy("category_vendors", "vendor", 
      creator=lambda vendor: CategoryVendorMap(vendor=vendor))

class Vendor(db.Model):
    __tablename__ = 'Vendor'

    vendor_id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True, nullable=False)

score 1 · Answer 1 · answered Mar 10 '20 at 19:05

You can try to use Hybrid Attributes and have the custom logic in the .setter:

@hybrid_property
def default_vendor_id(self):
    res = [_cv for _cv in self.category_vendors if _cv.is_default]
    return res and res[0].vendor_id or None

@default_vendor_id.setter
def default_vendor_id(self, value):
    # @note: need thorough testing in case a new vendor is added and set to be a default
    # also new objects will not have the `vendor_id` set yet
    for _cv in self.category_vendors:
        _cv.is_default = bool(_cv.vendor_id == value)

However, the setter needs to be tested really well to cover the corner cases, some of which are mentioned in the comment.

You might also consider creating a check constraint (or a unique filtered index) on the CategoryVendorMap table to make sure that there is a most only one is_default truthy value per category.

Another option to consider is to actually move the is_default flag from the relationship table to the Category model directly. In this case, of course, you must validate that the default vendor is one of the existing ones.

Update a sqlalchemy association proxy target when updating the parent ORM table

1 Answers1