如何在sqlalchemy中左外联接?

本文介绍了如何在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()