[FIXED] Flask sqlalchemy query using marshmallow not returning nested query result

Issue

I am trying to invoke below mentioned query and not seeing the department_info field in the json response .If I use name "Department", the department details are returning.Is there any way to use different name for the nested query result? .Please see the query,model and marshmallow schema detail below

resp = db.session.query(Employee,Department).select_from(Employee).outerjoin(Department,Employee.department_id== Department.id).filter(Employee.id == emp_id).first()

data = EmployeeSchema.dump(resp)
return jsonify(data)



class Employee(db.model)
  id = db.column(db.Integer, primary_key=True)
  name = db.column(db.String(45))
  department_id = db.Column(db.Integer. db.ForeignKey(Department.id))

class Department(db.model)
  id = db.column(db.Integer, primary_key=True)
  name = db.column(db.String(45))
  
class DepartmentSchema(ma.Schema):
    class Meta:
        fields = ('id', 'name')

class EmployeeSchema(ma.Schema):
    class Meta:
        fields = ('id', 'name', 'department_info')
    department_info = ma.Nested(DepartmentSchema, many=True)

Solution

You also need to have the relationship defined in your models (and it needs to be the same name as your nested Marshmallow object.

class Employee(db.model):
    id = db.column(db.Integer, primary_key=True)
    name = db.column(db.String(45))
    department_id = db.Column(db.Integer. db.ForeignKey(Department.id))
    department_info = db.relationship("Department", primaryjoin="Department.id==Employee.department_id")

Answered By - PGHE