Python SQLAlchemy 强制左连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19934177/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLAlchemy force Left Join
提问by SIkwan
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.
我有一个小问题。我有两个 SQL 表（ip 和客户端）由第三个（ip_client）绑定。我没有使用 SQLAlchemy 附带的多对多关系，因为我的一些查询发生了复杂的过滤。
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.
除了一个愚蠢的用例外，一切都很好。我想在没有客户端的情况下列出所有 IP，没有外部联接我无法做到。
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.
外连接需要几秒钟，而左连接的相同查询是即时的。但无法避免 SQL Alchemy 创建的 INNER JOIN。我尝试了所有关系，并移动了我的外键，但它保留了 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).
基于相同的问题，每当我尝试使用显示客户端数量的列列出所有 ips 时，查询将不会返回 0 个客户端的 ips（内部联接的自然行为）。
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 ?
在查询创建或连接函数中是否有一个技巧来强制 LEFT JOIN ？
采纳答案by van
Just use outerjoininstead of joinwhere appropriate:
只需在适当outerjoin的join地方使用而不是：
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 JOINis another issue, where having indiceson ForeignKeycolumns might be of tremendous help.
an 的性能OUTER JOIN是另一个问题，indices在ForeignKey列上可能会有很大帮助。
