python - tables - sqlalchemy models

¿Cómo unirse en múltiples tablas en SQLAlchemy?

Question

Tengo algunas tablas de notificación diferentes, y me gustaría realizar una unión entre todas ellas para mostrarle al usuario todas sus notificaciones. Sin embargo, la unión no está funcionando como debería.

Código de 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)

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

SQL generado

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

Espero algo en esta línea

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

Además, ¿hay alguna manera de ordenar los resultados de la unión agregada de SQLAlchemy?

EDITAR

Debo mencionar que sqlalchemy.sql.union() produce el SQL correcto, pero no sé cómo utilizarlo desde el ORM (devolver / contar los registros).

Answer 1

No creo que esto pueda funcionar con una unión, incluso suponiendo que la consulta se haya generado como esperabas. Está consultando tres tipos de objetos diferentes. Cuando el ORM recupera las filas de la base de datos, no veo una forma de asignar las filas a la clase correcta.

Una UNIÓN en este caso no tiene mucho sentido ya que la tercera columna tiene un significado diferente en las tres tablas.

Debe hacer las tres consultas por separado, a menos que sus tres tipos de notificación hereden de una clase común mapeada por ORM. En ese caso, SQLAlchemy admite consultar los cuatro tipos de una sola vez, aunque no con UNIÓN.

Answer 2

Sin duda, es posible unir diferentes tablas con sqlalchemy, solo tiene que especificar qué columnas va a extraer de cada tabla para que se puedan combinar correctamente. Digamos que tiene entidades A y B , haga esto:

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)

En este caso, todo lo que debe tener en cuenta es que si A.someColumn y B.someOtherColumn no pueden ser forzados al mismo tipo, su dbms podría enloquecer :)

Para un ejemplo en el "mundo real", vea la línea 85, 87 y 90:

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

Eso crea una subconsulta de dos tablas diferentes unidas juntas. Esa subconsulta se usa luego en una unión, y sus columnas se acceden a través de .c.column como de costumbre.