Sqlalchemy: How to Join Several Tables by One Query

Flask SQL Alchemy Join Multiple Tables

@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!

SQLAlchemy query from multiple tables

Reference the individual columns from multiple tables in you query, plus make sure you join to additional tables.

guests=db.session.query(Appointments.time,Clients.name).join(Clients).filter(Appointment.clientnumber==Clients.clientnumber).filter(Appointments.weekyr==weekyrnum).all()

If all you actually want is the guests but use Appoitments in you filter then you also just need to add a join.

guests=db.session.query(Clients).join(Appointments).filter(Appointments.clientnumber==Clients.clientnumber).filter(Appointments.weekyr==weekyrnum).all()

SQLAlchemy Join to retrieve data from multiple tables

So it seems that I need to use a different approach to the query, and that it returns a tuple of objects which I then need to parse.

What worked is:

 a = db.session.query(User, UserGroups, Areas
 ).filter(User.user_id==1
 ).join(UserGroup,User.usergroup==UserGroup.group_id
 ).join(Areas, User.area==Areas.area_id
 ).first()

The rest remaining the same. This then returned a tuple that I could parse where the data from User is a[0], from UserGroups is a[1], and Areas is a[2]. I can then access the group_name column with a[1].group_name etc.

Hopefully this helps someone else who's trying to work with this!

JOIN same table twice with aliases on SQLAlchemy

I figured this out. Here are the classes that are used in my Flask app:

class User(Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = Column(db.String(80), unique=True, nullable=False)
    skills = db.relationship('UserSkill')

class Skill(Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = Column(db.String(80))

class UserSkill(Model):
    status = db.Column(db.Enum(SkillStatus))
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), primary_key=True)
    skill_id = db.Column(db.Integer, db.ForeignKey('skills.id'), primary_key=True)
    skill = db.relationship("Skill")

So, the actual code would look like this:

from sqlalchemy.orm import aliased

userSkillF = aliased(UserSkill)
userSkillI = aliased(UserSkill)
skillF = aliased(Skill)
skillI = aliased(Skill)
            
db.session.query(User.id, User.username,\
         func.group_concat(func.distinct(skillF.name)).label('skills'),\
         func.group_concat(func.distinct(skillI.name)).label('other_skills')).\
    join(userSkillF, User.skills).\
    join(userSkillI, User.skills).\
    join(skillF, userSkillF.skill).filter(skillF.id.in_(skillIds)).\
    join(skillI, userSkillI.skill).\
    group_by(User.id).all()

Many thanks Ilja Everilä, fresh look on SqlAlchemy docs made me understand aliased now.

---

Related Topics