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.

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()