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