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

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