Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Flask/SQLAlchemy - Difference between association model and association table for many-to-many relationship?

I started learning this stuff from the Flask Mega Tutorial. When he gets into Many-to-Many relationships, he creates an association table like this:

followers = db.Table('followers',
    db.Column('follower_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('followed_id', db.Integer, db.ForeignKey('user.id'))
)

As I was searching for ways to add some metadata regarding a specific association between models, I found that you can store this kinda thing in the association table.. However the example of this I've found seems to make the association table an actual model.

class DepartmentEmployeeLink(Base):
    __tablename__ = 'department_employee_link'
    department_id = Column(Integer, ForeignKey('department.id'), primary_key=True)
    employee_id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    extra_data = Column(String(256))
    department = relationship(Department, backref=backref("employee_assoc"))
    employee = relationship(Employee, backref=backref("department_assoc"))

What is the difference between these two methods? Is the model method required to store metadata in the association table or can the same thing be accomplished with the top method?

Thanks!

like image 505
Chockomonkey Avatar asked May 22 '15 22:05

Chockomonkey


People also ask

How do you create a many to many relationship in SQLAlchemy?

Python Flask and SQLAlchemy ORM Many to Many relationship between two tables is achieved by adding an association table such that it has two foreign keys - one from each table's primary key.

How do you make a many to many relationship in Flask?

You add a tags class variable to the Post model. You use the db. relationship() method, passing it the name of the tags model ( Tag in this case). You pass the post_tag association table to the secondary parameter to establish a many-to-many relationship between posts and tags.


1 Answers

My apologies, I finally stumbled across the answer in the SQLAlchemy docs...

https://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#many-to-many

...where they explicitly define the difference:

Many to Many adds an association table between two classes.

association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')),
    Column('right_id', Integer, ForeignKey('right.id'))
)

The association object pattern is a variant on many-to-many: it’s used when your association table contains additional columns beyond those which are foreign keys to the left and right tables. Instead of using the secondary argument, you map a new class directly to the association table.

class Association(Base):
    __tablename__ = 'association'
    left_id = Column(Integer, ForeignKey('left.id'), primary_key=True)
    right_id = Column(Integer, ForeignKey('right.id'), primary_key=True)

    extra_data = Column(String(50))

    left = relationship('Left', backref=backref('right_association'))
    right = relationship('Right', backref=backref('left_association'))

Where "Right" and "Left" are tables, defined normally:

class Left(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key = True)
    ...

class Right(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key = True)
    ...

So it's basically creating an association object to reference this extra information if you need to store anything in the association, otherwise it's not necessary to use the ORM layer and you can just create an association table.

like image 164
Chockomonkey Avatar answered Oct 16 '22 06:10

Chockomonkey