如何在sqlalchemy中左外联接? [英] how left outer join in sqlalchemy?

问题描述

我想在sqlalchemy中实现左外部联接.sql查询是这样的:

I want implement left outer join in sqlalchemy. the sql query is like this:

select * from skills left join user__skill on user__skill.skill_id=skills.id and  user__skill.user_id=4

我在sqlalchemy中写的是:

and what i wrote in sqlalchemy is :

skills = db.session.query(Skill, User_Skill.skill_id).\
            outerjoin(User_Skill, User_Skill.skill_id==Skill.id and User_Skill.user_id==4).\
            order_by(Skill.name).all()

但它不会筛选用户并显示所有用户的技能.

but it doesn't filter for a user and show all users skills.

我如何编写此代码?

推荐答案

使用 sqlalchemy 中的 and _ 加入 join 条件

from sqlalchemy import and_

skills = db.session.query(Skill, User_Skill.skill_id).\
            outerjoin(User_Skill, and_(User_Skill.skill_id==Skill.id, 
                                       User_Skill.user_id==4)).\
            order_by(Skill.name).all()

---

错误的旧答案(不同结果):

---

Old, wrong answer (different results):

使用 .filter 限制结果.将查询更改为:

Use .filter to limit your results. Change your query to:

skills = db.session.query(Skill, User_Skill.skill_id).\
            outerjoin(User_Skill, User_Skill.skill_id==Skill.id).\
            filter(User_Skill.user_id==4).\
            order_by(Skill.name).all()

这篇关于如何在sqlalchemy中左外联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！