CODEWITHSUNDEEP
mysqlflaskjoinflask-sqlalchemyrelationship
Valentine Sean
Valentine Sean

Reputation: 129

JOIN Query Flask SQLAlchemy Related Tables using relationships table

I want to define two related tables which are student and mentor and their relationship has to be defined through a relationship table student_mentor on a database level. They have to be mapped to Flask app classes using Flask SQLAlchemy library. How can I relate the tables using Flask SQLAlchemy relationship and JOIN student and mentor tables during a query, (I neglected some fields of the tables).

The following are the classes of the models:

student:

class Student(db.Model, Serializer):
  id = db.Column(db.Integer, primary_key=True)
  #some columns...

mentor:

class Mentor(db.Model, Serializer):
  id = db.Column(db.Integer, primary_key=True)
  #some columns...

student_mentor

class StudentMentor(db.Model, Serializer):
  student_id = db.Column(db.Integer) # id column of student table
  mentor_id = db.Column(db.Integer) # id column of mentor table

I am looking forward to achieve my goal with a query of this nature:

query:

students = Student.query.order_by(desc(Student.created_at)).join(Mentor).paginate(page=1, per_page=5, error_out=False)

serialization:

students = json.loads(dumps(Student.serialize_list(students.items), default=str))

serializer:

from sqlalchemy.inspection import inspect

class Serializer(object):
    def serialize(self):
        return {c: getattr(self, c) for c in inspect(self).attrs.keys()}

    @staticmethod
    def serialize_list(l):
        return [m.serialize() for m in l]

Upvotes: 0

Views: 956

Answers (1)

jorzel
jorzel

Reputation: 1346

You don't have direct relationship between Student and Mentor, so you must join using StudentMentor model:

students = Student.query
    .join(StudentMentor, Student.id == StudentMentor.student_id)
    .join(Mentor, StudentMentor.mentor_id == Mentor.id)
    .order_by(desc(Student.created_at))
    .paginate(page=1, per_page=5, error_out=False)

For better joining you should use foreign keys in StudentMentor model:

class StudentMentor(db.Model, Serializer):
  student_id = db.Column(db.Integer, ForeignKey('student.id')) # id column of student table
  mentor_id = db.Column(db.Integer, ForeignKey('mentor.id') # id column of mentor table

Upvotes: 0

Related Questions