JOIN same table twice with aliases on SQLAlchemy
19,448
Solution 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.
Solution 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()
Related videos on Youtube
Author by
andnik
Python, JS/TS, Rust. I connect business needs with tech world, manage projects, provide technical expertise.
Updated on June 04, 2022Comments
-
andnik almost 2 years
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.
-
Ilja Everilä over 6 years"Using Aliases" is a good start, then "Querying with Joins".
-