how left outer join in sqlalchemy?

Question

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

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.

how can i write this code?

Answer 1

EDIT: Use and_ from sqlalchemy to join join conditions

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

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