43

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!

Chockomonkey
  • 3,895
  • 7
  • 38
  • 55
  • 1
    I am coming to this post after precisely the same journey. Started with Flask Mega Tutorial, just came across the article with the example table above. – dannypernik Sep 11 '22 at 02:54

1 Answers1

59

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.

Chockomonkey
  • 3,895
  • 7
  • 38
  • 55
  • 1
    @oski86 you're welcome! Sorting out the difference between the two was a big light-bulb-moment for me. It's all in the application of the `extra_data` column in the object pattern! If you don't need extra data tied to a specific pair of models, then you don't need the association object--a simpler association table will suffice! – Chockomonkey May 29 '18 at 19:15
  • 1
    @Chockomonkey Was searching for this. Please clear this for me. Would this be like a one-to-many relation now between 'Association' and the other models? In that case we'll have to define the relationship in those models as well? Would be of great help. – New Guy Jan 14 '19 at 14:30
  • 1
    @NewGuy Good question, thanks for asking! You found where my answer was lackluster, and I've since updated it with the fleshed-out relationship fields and added some table definitions too for clarity. Hope this helps! – Chockomonkey Jan 15 '19 at 00:59
  • updated doc link: https://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#many-to-many – user2682863 Feb 25 '19 at 04:14
  • for many-to-many it should be `back_populates` instead of `backref` – user2682863 Feb 25 '19 at 13:38
  • 1
    @user2682863 I looked into this today. The documentation suggests that backref is legitimately used in this case, and in fact ends up calling back_populates itself. It is a shorthand in that when backref is used in the definition of the relationship in the `Association` object, it does not require the explicit definition of a relationship in the `Left` and `Right` models. One could argue that explicit is better, but indeed either will do. – Chockomonkey Feb 26 '19 at 00:13