0

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?

Tavakoli
  • 1,303
  • 3
  • 18
  • 36
  • Have you tried comparing your sql with sql generated by sqlalchemy? – Adrian Krupa Mar 20 '19 at 09:57
  • how i must create it? – Tavakoli Mar 20 '19 at 09:59
  • http://nicolascadou.com/blog/2014/01/printing-actual-sqlalchemy-queries/ You can just print your query (without `.all()`) or use specific sql dialect to see sql sent to the database. – Adrian Krupa Mar 20 '19 at 10:01
  • yes. It is like this: `SELECT skills.id AS skills_id, skills.name_en AS skills_name_en, skills.name_fa AS skills_name_fa, user__skill.skill_id AS user__skill_skill_id FROM skills LEFT OUTER JOIN user__skill ON user__skill.skill_id = skills.id ORDER BY skills.name` there is not `user__skill.user_id=4` condition in the generated query. – Tavakoli Mar 20 '19 at 10:06
  • For background on why using the `and` **operation** does not work: https://stackoverflow.com/questions/42681231/sqlalchemy-unexpected-results-when-using-and-and-or – Ilja Everilä Mar 20 '19 at 15:48

1 Answers1

2

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()
Adrian Krupa
  • 1,877
  • 1
  • 15
  • 24