Flask sqlalchemy how to manage additional information in a many-to-many relationship

user_563236 0
Viewed 136

Consider the following many-to-many relationship:

class Hashes(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  hash = db.Column(db.String, nullable=False, unique=True)

xref_hashes_users = db.Table("xref_hashes_users",
  db.Column('hash', db.ForeignKey('hashes.id'), primary_key=True),
  db.Column('user', db.ForeignKey('user.id'), primary_key=True))

class User(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  email = db.Column(db.String)
  hashes = db.relationship("Hashes", secondary="xref_hashes_users", backref="users")

Let's say I want to allow users to store some additional information about their hashes, perhaps a label. It makes sense to me that given this is a user-specific piece of information about a hash, it would go in the association table like:

xref_hashes_users = db.Table("xref_hashes_users",
  db.Column('hash', db.ForeignKey('hashes.id'), primary_key=True),
  db.Column('user', db.ForeignKey('user.id'), primary_key=True),
  db.Column('label', db.String))

Given this schema, is it possible to use the ORM to add/remove/update labels? How would I do this?

flask-sqlalchemy python
1 Answers

It looks like the answer is to use an Association Object to store the extra data.

https://docs-sqlalchemy.readthedocs.io/ko/latest/orm/basic_relationships.html#association-object

So my example becomes:

class Hashes(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  hash = db.Column(db.String, nullable=False, unique=True)
  users = db.relationship("UserHashAssociation", back_populates="hashes")

class User(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  email = db.Column(db.String)
  hashes = db.relationship("UserHashAssociation", back_populates="users")

class UserHashAssociation(db.Model):
  __tablename__ = "xref_hashes_users"
  user_id = db.Column(db.Integer, db.ForeignKey("user.id"), primary_key=True)
  hash_id = db.Column(db.Integer, db.ForeignKey("hashes.id"), primary_key=True)
  label = db.Column(db.String)
  users = db.relationship("User", back_populates="hashes")
  hashes = db.relationship("Hashes", back_populates="users")

Then I am able to update the label like:

hash = Hashes(hash=hash_string)
user_hash_association = UserHashAssociation(label="foo", user_id=user.id)
hash.users.append(user_hash_association)
db.session.add(hash)
db.session.commit()
user_5632360
Related