SQLAlchemy 中的右外部联接 [英] RIGHT OUTER JOIN in SQLAlchemy

问题描述

我有两个表 beard 和 mustache 定义如下:

I have two tables beard and moustache defined below:

+--------+---------+------------+-------------+
| person | beardID | beardStyle | beardLength |
+--------+---------+------------+-------------+

+--------+-------------+----------------+
| person | moustacheID | moustacheStyle |
+--------+-------------+----------------+

我在 PostgreSQL 中创建了一个 SQL 查询，它将组合这两个表并生成以下结果:

I have created a SQL Query in PostgreSQL which will combine these two tables and generate following result:

+--------+---------+------------+-------------+-------------+----------------+
| person | beardID | beardStyle | beardLength | moustacheID | moustacheStyle |
+--------+---------+------------+-------------+-------------+----------------+
| bob    | 1       | rasputin   | 1           |             |                |
+--------+---------+------------+-------------+-------------+----------------+
| bob    | 2       | samson     | 12          |             |                |
+--------+---------+------------+-------------+-------------+----------------+
| bob    |         |            |             | 1           | fu manchu      |
+--------+---------+------------+-------------+-------------+----------------+

查询:

SELECT * FROM beards LEFT OUTER JOIN mustaches ON (false) WHERE  person = "bob"
UNION ALL
SELECT * FROM beards b RIGHT OUTER JOIN mustaches ON (false) WHERE  person = "bob"

但是我无法创建它的 SQLAlchemy 表示.我尝试了几种方法，从实现 from_statement 到 outerjoin 但都没有真正奏效.有人可以帮我吗?

However I can not create SQLAlchemy representation of it. I tried several ways from implementing from_statement to outerjoin but none of them really worked. Can anyone help me with it?

推荐答案

来自 @Francis P 的 建议我想出了这个片段:

From @Francis P's suggestion I came up with this snippet:

q1 = session.\
     query(beard.person.label('person'),
           beard.beardID.label('beardID'),
           beard.beardStyle.label('beardStyle'),
           sqlalchemy.sql.null().label('moustachID'),
           sqlalchemy.sql.null().label('moustachStyle'),
     ).\
     filter(beard.person == 'bob')

q2 = session.\
     query(moustache.person.label('person'),
           sqlalchemy.sql.null().label('beardID'), 
           sqlalchemy.sql.null().label('beardStyle'),
           moustache.moustachID,
           moustache.moustachStyle,
     ).\
     filter(moustache.person == 'bob')

result = q1.union(q2).all()

然而，这是可行的，但您不能将其称为答案，因为它看起来像是一种黑客行为.这是 sqlalchemy 中应该有 RIGHT OUTER JOIN 的另一个原因.

However this works but you can't call it as an answer because it appears as a hack. This is one more reason why there should be RIGHT OUTER JOIN in sqlalchemy.

这篇关于SQLAlchemy 中的右外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！