SQLAlchemy force Left Join

Question

I have a little problem. I have two SQL tables (ip and client) bound by a third one (ip_client). I did not use the many to many relationships shipped with SQLAlchemy because of complex filtering occurring on some of my queries.

Everything is fine except for one silly use case. I want to list all the IP without clients, and I can't do it without an outer join.

Outer Join takes several seconds where the same query with Left Join is instant. But impossible to avoid the INNER JOIN created by SQL Alchemy. I tried all relationships, and to move my foreign keys but it keeps an INNER JOIN.

Based on the same issue, whenever I try to list all my ips with a column showing the number of clients, the query won't return ips with 0 clients (natural behavior for an INNER JOIN).

Is there a way to force that ?

By the way this is my query :

query = (session.query(Ip, func.count(Client.id))
    .join(ClientIp, ClientIp.ip_id==Ip.id)
    .join(Client, Client.id==ClientIp.client_id)
    .group_by(Ip.id))

Is there a trick in the query creation or the join function to force a LEFT JOIN ?

Answer 1

Just use outerjoin instead of join where appropriate:

query = (session.query(Ip, func.count(Client.id)).
    outerjoin(ClientIp, ClientIp.ip_id==Ip.id).
    outerjoin(Client, Client.id==ClientIp.client_id).
    group_by(Ip.id)
)

Performance of an OUTER JOIN is another issue, where having indices on ForeignKey columns might be of tremendous help.