SQLAlchemy Coalesce and Join

Question

I'm having a lot of trouble converting my sql query to sqlalchemy. I haven't been able to find any resources doing what I am trying to do.

The query I am trying to convert is:

SELECT 
     COALESCE(d.manager_name, e.name) AS name,
     COALESCE(d.department_name, e.department_name) AS department
FROM employee e
LEFT JOIN department d ON e.id = d.id 
WHERE e.date = '2018-11-05'

In sqlalchemy I came up with:

  query = self.session.query(
            func.coalesce(Department.manager_name, Employee.name),
            func.coalesce(Department.department_name, Employee.department_name)).join(Department, 
                    Employee.id == Department.id,
                ).filter(
                    Employee.date == '2018-11-05',
                )

But keep getting the error:

sqlalchemy.exc.InvalidRequestError: Can't join table/selectable 'Department' to itself.

WHY?! The statements are exact!

Related: https://stackoverflow.com/questions/31766950/sqlalchemy-query-shows-error-cant-join-table-selectable-workflows-to-itself?rq=1 — Ilja Everilä, Nov 06 '18 at 05:39

score 3 · Accepted Answer · answered Nov 06 '18 at 05:49

Since Department is the leftmost item in your query, joins take place against it. To control what is considered the first – or the "left" – entity in the join use Query.select_from():

query = self.session.query(
        func.coalesce(Department.manager_name, Employee.name),
        func.coalesce(Department.department_name, Employee.department_name)).\
    select_from(Employee).\
    outerjoin(Department, Employee.id == Department.id).\
    filter(Employee.date == '2018-11-05')

This behaviour is also explained in the ORM tutorial under "Querying with Joins", and Query.join(): "Controlling what to Join From".

Your query construct was also using Query.join(), though the raw SQL had LEFT JOIN. In that case Query.outerjoin() or join(..., isouter=True) should be used.

SQLAlchemy Coalesce and Join

1 Answers1