Serialize extra field in many-to-many junction table when querying associated data table

Question

I am looking for a way to add an extra field from an association-table of a many-to-many relationship to a custom serializer property.

My goal is to add the extra field is_main from the helper table persons to the output of Task.serialize.

I found a similiar question here, but it uses Marshmallow which I want to avoid.

# Model definitions 

class Task(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    persons = db.relationship('Person', secondary=persons, lazy='subquery',
                              backref=db.backref('tasks', lazy=True))

    @property
    def serialize(self):
        return {
            'id': self.id,
            'persons': [person.serialize for person in self.persons]  # each task has a list of up to two persons
        }


class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    surname = db.Column(db.String(40), unique=True, nullable=True)
    name = db.Column(db.String(60), unique=False, nullable=True)

    @property
    def serialize(self):
        return {
            'id': self.id,
            'surname': self.surname,
            'name': self.name
        }


# many to many helper table with an extra field
persons = db.Table('persons',
                   db.Column('id_person', db.Integer, db.ForeignKey('person.id'), primary_key=True),
                   db.Column('id_task', db.Integer, db.ForeignKey('task.id'), primary_key=True),
                   db.Column('is_main', db.Boolean, default=True)
                   )

I tried to add the extra field using the default relationship definition but that didnt work.

Adding the attribute to the Person serialize property is also not possible as I see it because one person can have multiple relationships to a task with different is_main values.

score 0 · Accepted Answer · answered Sep 04 '22 at 05:25

After digging into the awesome SQLAlchemy documentation I found a solution.

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 relationship.secondary argument, you map a new class directly to the association table. The left side of the relationship references the association object via one-to-many, and the association class references the right side via many-to-one.

https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#association-object

Working solution:

# many-to-many association class
class TaskPersonAssociation(db.Model):
    __tablename__ = "tasks_persons_association_table"
    id_person = db.Column(db.ForeignKey("person.id"), primary_key=True)
    id_task = db.Column(db.ForeignKey("task.id"), primary_key=True)
    is_main = db.Column('is_main', db.Boolean, default=True)
    person = db.relationship("Person", back_populates="tasks")
    task = db.relationship("Task", back_populates="persons")

    @property
    def serialize_with_person(self):
        return {
            'is_main': self.is_main,
            'person': self.person.serialize
        }

class Task(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    
    persons = db.relationship('TaskPersonAssociation', back_populates='task')

    @property
    def serialize(self):
        return {
            'id': self.id,
            'persons': [person.serialize_with_person for person in self.persons]  # each task has a list of persons
        }

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    surname = db.Column(db.String(40), unique=True, nullable=True)
    name = db.Column(db.String(60), unique=False, nullable=True)

    tasks = db.relationship('TaskPersonAssociation', back_populates='person')

    @property
    def serialize(self):
        return {
            'id': self.id,
            'surname': self.surname,
            'name': self.name
        }

Serialize extra field in many-to-many junction table when querying associated data table

1 Answers1