如何在SQLAlchemy中跨多个表联合?

2024-03-29 07:14:18 发布

您现在位置:Python中文网/ 问答频道 /正文

我有几个不同的通知表,我想在它们之间执行联合,以向用户显示它们的所有通知。然而,工会并没有像它应该做的那样工作。

Python代码

def _get_notifications_query(self, unconfirmed_only=True):
    '''
    Return base query to return this users notifications.

    @param unconfirmed_only
    @return Query object
    '''        
    requests = (
        DBSession.query(FriendshipRequestNotification)
        .outerjoin(UserFriendshipRequestNotification,
                   UserFriendshipRequestNotification.notification_id==FriendshipRequestNotification.id)
        .filter(UserFriendshipRequestNotification.user_id==self.id))
    confirmations = (
        DBSession.query(FriendshipConfirmationNotification)
        .outerjoin(UserFriendshipConfirmationNotification,
                   UserFriendshipConfirmationNotification.notification_id==FriendshipConfirmationNotification.id)
        .filter(UserFriendshipConfirmationNotification.user_id==self.id))
    comments = (
        DBSession.query(CommentNotification)
        .outerjoin(UserCommentNotification,
                   UserCommentNotification.notification_id==CommentNotification.id)
        .filter(UserCommentNotification.user_id==self.id))

    if unconfirmed_only:
        requests.filter(UserFriendshipRequestNotification.is_confirmed==False)
        confirmations.filter(UserFriendshipConfirmationNotification.is_confirmed==False)
        comments.filter(UserCommentNotification.is_confirmed==False)

    return requests.union(confirmations, comments)

使用:user._get_notifications_query(unconfirmed_only=False).all()

生成的SQL

SELECT anon_1.friendship_request_notifications_id AS anon_1_friendship_request_notifications_id, anon_1.friendship_request_notifications_created_at AS anon_1_friendship_request_notifications_created_at, anon_1.friendship_request_notifications_requester_id AS anon_1_friendship_request_notifications_requester_id 
FROM (SELECT friendship_request_notifications.id AS friendship_request_notifications_id, friendship_request_notifications.created_at AS friendship_request_notifications_created_at, friendship_request_notifications.requester_id AS friendship_request_notifications_requester_id 
FROM friendship_request_notifications LEFT OUTER JOIN users_friendship_request_notifications ON users_friendship_request_notifications.notification_id = friendship_request_notifications.id 
WHERE users_friendship_request_notifications.user_id = ? UNION SELECT friendship_confirmation_notifications.id AS friendship_confirmation_notifications_id, friendship_confirmation_notifications.created_at AS friendship_confirmation_notifications_created_at, friendship_confirmation_notifications.accepter_id AS friendship_confirmation_notifications_accepter_id 
FROM friendship_confirmation_notifications LEFT OUTER JOIN users_friendship_confirmation_notifications ON users_friendship_confirmation_notifications.notification_id = friendship_confirmation_notifications.id 
WHERE users_friendship_confirmation_notifications.user_id = ? UNION SELECT comment_notifications.id AS comment_notifications_id, comment_notifications.created_at AS comment_notifications_created_at, comment_notifications.comment_id AS comment_notifications_comment_id 
FROM comment_notifications LEFT OUTER JOIN users_comment_notifications ON users_comment_notifications.notification_id = comment_notifications.id 
WHERE users_comment_notifications.user_id = ?) AS anon_1

我希望有这样的事情发生

SELECT * FROM friendship_request_notifications
UNION
SELECT * FROM friendship_confirmation_notifications
UNION 
SELECT * FROM comment_notifications

另外,是否有任何方法可以对来自SQLAlchemy的聚合联合结果进行排序?

编辑

我应该提到sqlalchemy.sql.union()生成了正确的SQL,但是我不知道如何从ORM(返回/计数记录)中利用它。


Tags: fromidrequestascommentqueryselectusers
2条回答

我不认为这可以与联合一起工作,即使假设查询是按预期生成的。您正在查询三种不同的对象类型。当ORM从数据库中获取行时,我看不到它将行映射到正确类的方法。

在这种情况下,联合没有多大意义,因为第三列在所有三个表中都有不同的含义。

您应该分别执行这三个查询,除非您的三个通知类型继承自公共ORM映射类。在这种情况下,SQLAlchemy支持同时查询四种类型,但不支持联合查询。

当然可以将不同的表与sqlalchemy联合起来,您只需指定从每个表中提取哪些列,就可以正确地组合它们。假设您有实体AB,请执行以下操作:

from sqlalchemy.sql.expression import label
...

a = session.query(label('col1', A.someColumn))
b = session.query(label('col1', B.someOtherColumn))
both = a.union_all(b)

在这种情况下,您需要注意的是,如果不能将A.someColumnB.someOtherColumn强制为同一类型,您的dbms可能会崩溃:)

有关“真实世界”中的示例,请参见第85、87和90行:

https://gerrit.wikimedia.org/r/#/c/147312/4/wikimetrics/metrics/rolling_active_editor.py

这将创建一个子查询,其中包含两个联合在一起的不同表。该子查询随后在联接中使用,其列通常通过.c.column访问。

相关问题 更多 >