Alex Rivera | Logout

How to union across multiple tables in SQLAlchemy?

Asked 2012-05-16T01:43:29.427
8

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_reques
Edit
Report

1 Answer

4

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.

answered 2012-05-16T05:44:04.297

Your Answer