JOIN same table twice with aliases on SQLAlchemy

Question

I am trying to port the following query to SQLAlchemy:

SELECT u.username, GROUP_CONCAT(DISTINCT userS.name) FROM Skills AS filterS  INNER JOIN UserSkills AS ufs ON filterS.id = ufs.skill_id INNER JOIN Users AS u ON ufs.user_id = u.id INNER JOIN UserSkills AS us ON u.id = us.user_id INNER JOIN Skills AS userS ON us.skill_id = userS.id WHERE filterS.name IN ('C#', 'SQL') GROUP BY u.id; 

I don't understand how to achieve AS statement in SQLAlchemy. Here is what I currently have:

# User class has attribute skills, that points to class UserSkill # UserSkill class has attribute skill, that points to class Skill db.session.query(User.id, User.username, func.group_concat(Skill.name).label('skills')).\    join(User.skills).\    join(UserSkill.skill).filter(Skill.id.in_(skillIds)).\    order_by(desc(func.count(Skill.id))).\    group_by(User.id).all() 

Please help.

Answer 1

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.

Answer 2

We can do the join without relationships as well. Explicitly mention the condition on join.

Example

    from sqlalchemy.orm import aliased      user1 = aliased(UserSkill)     user2 = aliased(UserSkill)      query_result = db.session.query(         func.distinct(User.id).label('user_id'),         User.username,     ).join(         user1,         User.id == user1.user_id,     ).join(         user2,         user2.id == User.id,     ).filter(         user1.user_id == id,     ).all()