如何在 SQLAlchemy 中跨多个表联合? [英] How to union across multiple tables in SQLAlchemy?

问题描述

我有几个不同的通知表，我想对所有这些表执行联合以向用户显示他们的所有通知.然而，工会并没有发挥应有的作用.

I have a few different notification tables, and I would like to perform a union across all of them to show the user all of their notifications. However, the union is not working as it should.

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()

Use: 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 的聚合联合结果进行排序?

Also, is there any way to sort the aggregated union results from SQLAlchemy?

编辑

我应该提到 sqlalchemy.sql.union() 生成正确的 SQL，但我不知道如何从 ORM 中使用它(返回/计算记录).

I should mention that sqlalchemy.sql.union() produces the correct SQL, but I dont know how to make use of that from the ORM (return/count the records).

推荐答案

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

I don't think this can work with a union, even supposing the query was generated as you expect. You're querying three different object types. When the ORM gets the rows back from the database, I don't see a way for it to map rows to the right class.

在这种情况下，UNION 没有多大意义，因为第三列在所有三个表中都有不同的含义.

A UNION in this case doesn't make much sense since the third column has a different meaning in all three tables.

您应该分别执行三个查询，除非您的三个通知类型继承自一个常见的 ORM 映射类.在这种情况下，SQLAlchemy 支持同时查询四种类型，尽管不使用 UNION.

You should do the three queries separately, unless your three notification types inherit from a common ORM-mapped class. In that case SQLAlchemy supports querying the four types all at once, although not with a UNION.

这篇关于如何在 SQLAlchemy 中跨多个表联合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！