JOIN Query Flask SQLAlchemy Related Tables using relationships table

Question

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]

I think you can try `secondary` parameter in relationship: https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#many-to-many. Similar case: https://stackoverflow.com/questions/7417906/sqlalchemy-manytomany-secondary-table-with-additional-fields — jorzel, Dec 29 '21 at 09:27
@jorzel when I hit a query like in the question I am getting an error: "sqlalchemy.exc.InvalidRequestError sqlalchemy.exc.InvalidRequestError: Don't know how to join to . Please use the .select_from() method to establish an explicit left side, as well as providing an explicit ON clause if not present already to help resolve the ambiguity." — Valentine Sean, Dec 30 '21 at 14:19

score 0 · Answer 1 · answered Dec 30 '21 at 21:27

0

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

answered Dec 30 '21 at 21:27

jorzel

1,216
1
8
12

The result is of the form: [{'name': 'student_name', 'mentors': ['']}]. How can I jsonify Mentor information?. '' is a string. – Valentine Sean Dec 31 '21 at 08:05
You should define a serializer function for `Mentor`. How you serialize `Student`? https://stackoverflow.com/questions/5022066/how-to-serialize-sqlalchemy-result-to-json – jorzel Dec 31 '21 at 09:23
I edited my question with new additions which are serialization of my Student and the Serialize class. – Valentine Sean Dec 31 '21 at 11:34
You probably need to have more sophisticated serializer that handle tuples: https://stackoverflow.com/questions/35952694/how-to-serialize-sqlalchemy-join-query-to-json – jorzel Dec 31 '21 at 12:12

JOIN Query Flask SQLAlchemy Related Tables using relationships table

1 Answers1