如何在sqlalchemy加入中添加一个子句

Question

我正在寻找一个查询，例如：

SELECT DISTINCT entity_rel.eidsub AS eidsub, entity_user_client.eidclient AS eidclient 
FROM entity_user_client
LEFT OUTER JOIN entity_rel ON entity_user_client.eidclient = entity_rel.eidobj AND entity_rel.reltype = :reltype_1 
LEFT OUTER JOIN entity_client ON entity_user_client.eidclient = entity_client.entityid
WHERE entity_user_client.eiduser IN (:userid_1)

使用以下代码：

euc_query = select(
        [EntityRel.c.eidsub, EntityUserClient.c.eidclient]
    ).distinct().select_from(
        EntityUserClient
    ).outerjoin(
        EntityRel,
        (EntityUserClient.c.eidclient == EntityRel.c.eidobj) & (
                EntityRel.c.reltype == 51)
    ).outerjoin(
        EntityClient,
        EntityUserClient.c.eidclient == EntityClient.c.entityid
    )
    if user_ids:  # filter by user
        euc_query = euc_query.where(EntityUserClient.c.eiduser.in_(user_ids))

但给出了错误：

attributeError：'join'对象没有属性'''

另外，在选择周围包裹着衍生物：

(SELECT DISTINCT entity_rel.eidsub AS eidsub, entity_user_client.eidclient AS eidclient 
FROM entity_user_client)
LEFT ...

不应该在那里，我不知道如何摆脱：(

Answer 1

应将连接到EntityUserClient并由select_from()：例如：

user_ids = (1, 2, 3, 4, 5)
euc_query = select(
        [EntityRel.c.eidsub, EntityUserClient.c.eidclient]
    ).distinct().select_from(
        EntityUserClient.outerjoin(
            EntityRel,
            (EntityUserClient.c.eidclient == EntityRel.c.eidobj) & (
                EntityRel.c.reltype == 51)
        ).outerjoin(
            EntityClient,
            EntityUserClient.c.eidclient == EntityClient.c.entityid
        )
    )
if user_ids:  # filter by user
    euc_query = euc_query.where(EntityUserClient.c.eiduser.in_(user_ids))
print(euc_query)

生产：

SELECT DISTINCT entity_rel.eidsub, entity_user_client.eidclient
FROM entity_user_client LEFT OUTER JOIN entity_rel ON entity_user_client.eidclient = entity_rel.eidobj AND entity_rel.reltype = :reltype_1 LEFT OUTER JOIN entity_client ON entity_user_client.eidclient = entity_client.entityid
WHERE entity_user_client.eiduser IN (:eiduser_1, :eiduser_2, :eiduser_3, :eiduser_4, :eiduser_5)