How to union across multiple tables in SQLAlchemy?

Question

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 code

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)

Use: user._get_notifications_query(unconfirmed_only=False).all()

SQL generated

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

I expect something along these lines

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

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

EDIT

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

Answer 1

It's certainly possible to union different tables with sqlalchemy, you just have to specify what columns you're fetching out of each table so they can be combined properly. Say you have entities A and B, do this:

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)

In this case, all you'd have to be aware of is that if A.someColumn and B.someOtherColumn can't be coerced to the same type, your dbms might freak out :)

For an example in the "real world", see line 85, 87, and 90:

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

That creates a subquery of two different tables unioned together. That subquery is then used later in a join, and its columns accessed via .c.column as usual.

Answer 2

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.

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

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.