在SQLAlchemy中使用别名两次连接相同的表格

9 浏览
0 Comments

在SQLAlchemy中使用别名两次连接相同的表格

我正在尝试将以下查询转换为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;

我不明白如何在SQLAlchemy中实现AS语句。以下是我目前的代码：

# User类具有指向UserSkill类的skills属性
# UserSkill类具有指向Skill类的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()

请帮忙。

0