JOIN same table twice with aliases on SQLAlchemy
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()
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.