Getting foreign key instead of relationship table in marshmallow_sqlalchemy schema

Question

I have a flask-sqlalchemy table composed of a relationship with 4 other tables as follows:

office_cardgroups = db.Table('office_cardgroups',
                             db.Column('officedata_id', db.Integer, db.ForeignKey('officedata.id')),
                             db.Column('cardgroups_id', db.Integer, db.ForeignKey('cardgroups.id'))
                             )

class OfficeData(BaseModel):
    __tablename__ = 'officedata'
    id = db.Column(db.Integer, primary_key=True)
    department = db.relationship('Department', lazy=True)
    cardgroups = db.relationship('Cardgroup', secondary=office_cardgroups, lazy=True)
    doorgroup = db.relationship('Doorgroup', lazy=True)
    office = db.relationship('Office', lazy=True)
    office_id = db.Column(db.Integer, db.ForeignKey('offices.id'))
    department_id = db.Column(db.Integer, db.ForeignKey('departments.id'))
    doorgroup_id = db.Column(db.Integer, db.ForeignKey('doorgroups.id'))

I have a Schema for this model as follows:

from app.globals import marshmallow
from marshmallow_sqlalchemy import fields
from app.models.office_data import OfficeData
from app.schemas.cardgroup import CardgroupSchema
from app.schemas.doorgroup import DoorgroupSchema
from app.schemas.office import OfficeSchema
from app.schemas.department import DepartmentSchema

class OfficeDataSchema(marshmallow.SQLAlchemyAutoSchema):
    class Meta:
        model = OfficeData
        cardgroups = fields.Nested(CardgroupSchema)
        doorgroup = fields.Nested(DoorgroupSchema)
        office = fields.Nested(OfficeSchema)
        department = fields.Nested(DepartmentSchema)
        include_relationships = True
        load_instance = True

However, when I dump the model, I only get the IDs of the nested tables not the actual data inside these tables.

Output:

 office_data_schema = OfficeDataSchema(many=True)
 user_office_data = office_data_schema.dump(user_office_data)
 app.logger.info(user_office_data)

[{'id': 68, 'office': 1, 'department': 32, 'cardgroups': [2], 'doorgroup': None}, {'id': 69, 'office': 1, 'department': 33, 'cardgroups': [2], 'doorgroup': None}, {'id': 70, 'office': 1, 'department': 34, 'cardgroups': [2], 'doorgroup': None}, {'id': 71, 'office': 1, 'department': 35, 'cardgroups': [2], 'doorgroup': None}]

An example for one of the relationship tables and how they should look like is Table Office:

class Office(BaseModel):
    __tablename__ = 'offices'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, nullable=False)
    shortname = db.Column(db.String, nullable=False)

In Flask-Admin, the the OfficeData model is actually displayed in the right manner not IDs so I think its a problem with the Schema definition.

Answer 1

I just ran into this too thinking that include_relationships = True would automatically populate the nested fields but also had to define the relationships in the Schema (as you've done with OfficeDataSchema) but I think the nested fields need to be part of the OfficeDataSchema itself rather than Meta (one indentation level back), e.g.

class OfficeDataSchema(marshmallow.SQLAlchemyAutoSchema):
    class Meta:
        model = OfficeData
        include_relationships = True
        load_instance = True

    cardgroups = fields.Nested(CardgroupSchema)
    doorgroup = fields.Nested(DoorgroupSchema)
    office = fields.Nested(OfficeSchema)
    department = fields.Nested(DepartmentSchema)