Flask-SQLAlchemy Many-to-Many Relationships: Association Tables and Association Objects
Tags: flask, beginners
Published: 2023-11-21 21:59:01

SQLAlchemy provides two tools for managing many-to-many relationships:  Association Tables and Association Objects.  This blog will review how to use association tables and association objects in a Flask application using the Flask-SQLAlchemy extension.

## Association Tables

An association table, like a basic join table in SQLlite, is a database table that is used to keep track of the relationships between two other tables.  An association tables does not contain any information besides what is necessary to manage those relationships.

Say we have two models, `Teacher` and `Student`, that have a many-to-many relationship.  Each teacher has many students, and each student has many teachers.  Their relationship can be managed with an association table, `teacher_students`.  The only columns that `teacher_students` contains are the columns that refer to the tables for `Teacher` and Student.  `teacher_students` cannot be used to manage any additional information.

The relationship between `Teacher` and `Student` is created by making columns in `teacher_students` that contain foreign keys referring to the id columns of the `Teacher` and `Student` tables.

```
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import MetaData
from sqlalchemy.ext.associationproxy import association_proxy

metadata = MetaData(naming_convention={
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
})

db = SQLAlchemy(metadata=metadata)

teacher_students = db.Table(
    'teacher_students',
    metadata,
    db.Column('teacher_id', db.ForeignKey('teachers.id'), 
        primary_key=True),
    db.Column('student_id', db.ForeignKey('students.id'), 
        primary_key=True)
)
```

It isn't necessary to specify the data type of the columns in `teacher_students` because the type of data in each column is inferred from the type of data in the columns of the `Teacher` table and `Student` table.

Regarding the model columns that should be used in association table:

> It is also recommended, though not in any way required by SQLAlchemy, that the columns which refer to the two entity tables are established within either a unique constraint or more commonly as the primary key constraint; this ensures that duplicate rows won’t be persisted within the table regardless of issues on the application side.
> https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#many-to-many


To create the many-to-many relationship between `Teacher` and `Student`, each model is given a relationship() attribute that links them to each other.  `Teacher` is given a `students` attribute and `Student` is given a `teachers` attribute.  The connection between the models and `teacher_students` is provided by the `secondary` parameter in each model's relationship attribute, which points to the association table.

```
class Teacher(db.Model):
    __tablename__ = 'teachers'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    students = db.relationship('Student', secondary=teacher_students,
        back_populates='teachers')

    def __repr__(self):
        return f'<Teacher {self.id}>'

class Student(db.Model):
    __tablename__ = 'students'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    teachers = db.relationship('Teacher', secondary=teacher_students,     
        back_populates='students')

    def __repr__(self):
        return f'<Student {self.id}>'
```

Additional details and information about the `secondary` parameter can be found here:
https://docs.sqlalchemy.org/en/20/orm/relationship_api.html#sqlalchemy.orm.relationship.params.secondary

To see the students that a teacher has or the teachers that a student has, the `students` and `teachers` attributes can be called on instances of the `Teacher` and `Student` models:

```
>>> t1 = Teacher.query.first()
>>> t1
<Teacher 1>
>>> t1.students
[<Student 1>, <Student 3>, <Student 6>]
```

```
>>> s1 = Student.query.first()
>>> s1
<Student 1>
>>> s1.teachers
[<Teacher 1>, <Teacher 2>]
```

## Association Objects

What if, instead of only keeping track of the relationships between `Teacher` and `Student` instances, we wanted the join table to also keep track of the exams that each teacher administers and each student takes.  In that case, an association object would be used instead of an association table because it is necessary for the join table to hold extra information.  In other words, when the join table needs to contain more columns than just the foreign keys from other tables, an association object should be used.

> The association object pattern is a variant on many-to-many: it’s used when an association table contains additional columns beyond those which are foreign keys to the parent and child (or left and right) tables, columns which are most ideally mapped to their own ORM mapped class. 
> https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#association-object

In the example above, there are models for `Teacher` and `Student`.  To create an association object that keeps track of exams, along with the relationships between `Teacher` and `Student` instances, there needs to be a third model, `Exam`, which acts as the association object.  Like the association table used above, the `Exam` model's table is still given columns to keep track of the foreign keys of the many-to-many relationship models, but it can also contain columns for whatever other information is needed to be stored in the table.

```
class Exam(db.Model):
    __tablename__ = 'exams_table'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    teacher_id = db.Column(db.Integer, db.ForeignKey('teachers.id'))
    student_id = db.Column(db.Integer, db.ForeignKey('students.id'))

    teacher = db.relationship('Teacher', back_populates='exams')
    student = db.relationship('Student', back_populates='exams')

    def __repr__(self):
        return f'<Exam {self.id}>'
```

Instead of the `Teacher` and `Student` models having a direct, many-to-many relationship, they will now have one-to-many relationships with the `Exam` association object.

> In the association object pattern, the `relationship.secondary` parameter is not used; instead, a class is mapped directly to the association table. Two individual `relationship()` constructs then link first the parent side to the mapped association class via one to many, and then the mapped association class to the child side via many-to-one, to form a uni-directional association object relationship from parent, to association, to child. For a bi-directional relationship, four `relationship()` constructs are used to link the mapped association class to both parent and child in both directions.
> https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#association-object

Instead of `Teacher` having a `students` attribute, it has an `exams` attribute, while the `Student` model has its own `exams` attribute.

```
class Teacher(db.Model):
    __tablename__ = 'teachers'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    exams = db.relationship('Exam', back_populates='teacher')

    students = association_proxy('exams', 'student')

    def __repr__(self):
        return f'<Teacher {self.id}>'

class Student(db.Model):
    __tablename__ = 'students'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    exams = db.relationship('Exam', back_populates='student')

    teachers = association_proxy('exams', 'teacher')

    def __repr__(self):
        return f'<Student {self.id}>'

```

This means that, unlike with an association table, the relationship between `Teacher` and `Student` cannot be accessed directly from a relationship object in either model.  To make it easier to view the relationship between `Teacher` and `Student` now that they are linked by an association object, SQLAlchemy provides the Association Proxy tool.

> To enhance the association object pattern such that direct access to the Association object is optional, SQLAlchemy provides the Association Proxy extension. This extension allows the configuration of attributes which will access two “hops” with a single access, one “hop” to the associated object, and a second to a target attribute.
> https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#association-object

The association proxies in the above example (`students` in the `Teacher` model and `teachers` in the `Student` model) are provided (1) the name of the immediate target that is being used to manage the many-to-many relationship and (2) an attribute that is available in an instance of that target.  In this example the immediate target is the relationship attribute `exams`, which is the variable used to establish the one-to-many relationships between the `Teacher` and `Student` models and the `Exam` association object.  The attribute given to `association_proxy()` is the corresponding relationship variable in the `Exam` association object.

Now, the `Teacher` and `Student` models each have way to view the one-to-many relationship with the `Exam` association object, as well as a way to directly view the many-to-many relationships through the association proxies.  `Teacher`'s association proxy provides the value of the `students` associated with it while concealing, or skipping over, the association object.

```
>>> t1 = Teacher.query.first()
>>> t1
<Teacher 1>
>>> t1.exams
[<Exam 1>, <Exam 2>, <Exam 3>]
>>> t1.students
[<Student 1>, <Student 3>, <Student 6>]
```

By default, calling a model's association proxy attribute will return instances of the target object.  So, calling `Teacher.students` returns a list of all the instances of `Student` that have a relationship with a particular instance of `Teacher`.

Additional information about using association proxies can be found here:
> https://docs.sqlalchemy.org/en/20/orm/extensions/associationproxy.html