how left outer join in sqlalchemy?

Issue

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?

Solution

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

Answered By – Adrian Krupa

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published