Flask SQL Alchemy Join Multiple Tables

Question

I'm looking to join multiple tables and selecting specific columns using Flask-SqlAlchemy. I'm getting errors with my attempts as I believe my join statements are wrong

Any help would be appreciated

The objects "Question" and "Question" in the FROM clause have the same exposed names

models.py

class Survey(db.Model):
 __tablename__ = 'Survey'
 survey_id = db.Column(db.Integer, primary_key=True)
 description = db.Column(db.String)
 def __repr__(self):
 return '<Survey %r>' % self.description
class Question(db.Model):
 __tablename__ = 'Question'
 question_id = db.Column(db.Integer, primary_key=True)
 description = db.Column(db.String)
 survey_id = db.Column(db.Integer, db.ForeignKey('Survey.survey_id'), nullable=False)
 survey = db.relationship('Survey', backref=db.backref('questions', lazy=True))
 def __repr__(self):
 return '<Question %r>' % self.description
class Person(db.Model):
 __tablename__ = 'Person'
 person_id = db.Column(db.Integer, primary_key=True)
 description = db.Column(db.String)
 survey_id = db.Column(db.Integer, db.ForeignKey('Survey.survey_id'), nullable=False)
 survey = db.relationship('Survey', backref=db.backref('persons', lazy=True))
 def __repr__(self):
 return '<Person %r>' % self.description
class Answer(db.Model):
 __tablename__ = 'Answer'
 answer_id = db.Column(db.Integer, primary_key=True)
 description = db.Column(db.String)
 sentiment_azure = db.Column(db.Float)
 sentiment_google = db.Column(db.Float)
 question_id = db.Column(db.Integer, db.ForeignKey('Question.question_id'), nullable=False)
 person_id = db.Column(db.Integer, db.ForeignKey('Person.person_id'), nullable=False)
 question = db.relationship('Question', backref=db.backref('answers', lazy=True))
 person = db.relationship('Person', backref=db.backref('answers'), lazy=True)
 def __repr__(self):
 return '<Answer %r>' % self.description

routes.py

@app.route('/edit/<survey_id>')
def edit(survey_id):
 ans = db.session.query(Survey.description, Question.description, Answer.description).join(Survey).join(Question).join(Answer).join(Person).filter(Survey.survey_id == survey_id).all()
 print(ans)
 return 'OK'

Answer 1

I managed to find a way to do it, however I think there's probably a better approach. Flask documentation suggests the following, but I couldn't figure to connect all the four tables:

from sqlalchemy.orm import joinedload
query = Category.query.options(joinedload('posts'))

This is my solution:

@app.route('/edit/<survey_id>')
def edit(survey_id):
 q = db.session.query(Survey, Person, Question, Answer)\
 .join(Person, Person.survey_id == Survey.survey_id)\
 .join(Question, Question.survey_id == Survey.survey_id)\
 .join(Answer, Answer.question_id == Question.question_id).all()
 print(q)
 return 'OK'

Answer 2

@app.route('/edit/<survey_id>')
def edit(survey_id):
 q = db.session.query(Survey, Person, Question, Answer)
 .filter(Person.survey_id == Survey.survey_id,
 Question.survey_id == Survey.survey_id,
 Answer.question_id == Question.question_id).all()
 print(q)
 return 'OK'

Hope this will help!