从子查询中选择多个格式对象

Question

鉴于此FromClause：

q = (
    select([Review, Photo])
    .select_from(
        outerjoin(Review, Photo, false())
    ).apply_labels()
).union_all(
    select([Review, Photo])
    .select_from(
        outerjoin(Photo, Review, false())
    ).apply_labels()
)

我可以从中选择orm对象：

^{pr2}$

得出以下结果：

[(Review(id=1, thing_id=100, ...), None),
 (Review(id=2, thing_id=100, ...), None),
 (Review(id=3, thing_id=101, ...), None),
 (None,                            Photo(id=1, thing_id=100))]

我想将此连接到我的Thing表中，以给出：

[(Thing(100), Review(id=1, thing_id=100, ...), None),
 (Thing(100), Review(id=2, thing_id=100, ...), None),
 (Thing(101), Review(id=3, thing_id=101, ...), None),
 (Thing(100), None,                            Photo(id=1, thing_id=100)),
 (Thing(102), None,                            None)]

尝试1：选择

但是，当我尝试：

thing_q = select([Thing, Review, Photo]).select_from(
    outerjoin(Thing, q,
        (Review.thing_id == Thing.id) |
        (Photo.thing_id == Thing.id)
    )
)

发出的查询是：

SELECT
    things.id, things....,
    -- this should be selecting these from the subquery...
    reviews.id, reviews.thing_id, reviews....,
    photos.id, photos.thing_id, photos....,
FROM
    reviews, -- not from the tables, doing a CROSS JOIN!
    photos,  -- Look, another CROSS JOIN!
    things
    LEFT OUTER JOIN (
        SELECT
            reviews.id AS reviews_id,
            reviews.thing_id AS reviews_thing_id,
            reviews.... as reviews_...,
            photos.id AS photos_id,
            photos.thing_id AS photos_thing_id
            photos.... as photos...,
        FROM
            reviews
            LEFT OUTER JOIN photos ON 0
        UNION ALL
        SELECT
            reviews.id AS reviews_id,
            reviews.thing_id AS reviews_thing_id,
            reviews.... as reviews_...,
            photos.id AS photos_id,
            photos.thing_id AS photos_thing_id
            photos.... as photos...,
        FROM
            photos
            LEFT OUTER JOIN reviews ON 0
    ) ON reviews.thing_id = things.id OR photos.thing_id = things.id

做了一个交叉连接！如何从UNION ALLfrom子句中选择Review和{}？在

尝试2：从中选择实体

thing_q = s.query(Thing, Review, Photo).select_entity_from(
    outerjoin(Thing, q,
        (Review.thing_id == Thing.id) |
        (Photo.thing_id == Thing.id)
    )
)

它给出了：

  File "...\sqlalchemy\lib\sqlalchemy\sql\compiler.py", line 1397, in visit
    translate_dict[right.element.left] = selectable_
AttributeError: 'CompoundSelect' object has no attribute 'left'

是啊，神秘的错误。不知道我做错了什么。在

Answer 1

尝试使用^{}，因为它会将Thing放在最右边。下面的片段可能对您有用：

>>> query = session.query(Review, Photo).select_entity_from(q)
>>> query = query.add_entity(Thing)
>>> query = query.join(Thing,
...                    (Thing.id == Review.thing_id) |
...                    (Thing.id == Photo.thing_id))
>>> print query.first()  # Just an expectation...
(Review(id=1, thing_id=100, ...), None, Thing(100))