SQLAlchemy: Encuentra la diferencia entre las columnas de la matriz

Tengo una tabla llamada UnitOfWork que tiene 3 columnas cases_identified , cases_completed y cases_double_check , todos los cuales son matrices de enteros Postgresql.

¿Es posible escribir una consulta (o una propiedad híbrida) que encuentre los casos que se han identificado, pero que no están en las columnas completadas o de doble verificación?

Esto es lo que se me ocurrió, pero la expresión SQL no funciona:

 @hybrid_property def todo(self): return [x for x in self.cases_identified if x not in self.cases_completed and x not in self.cases_double_check] @todo.expression def todo(cls): return [x for x in cls.cases_identified if x not in cls.cases_completed and x not in cls.cases_double_check] 

El error que obtengo en una consulta de prueba es:

 test = Session.query(UnitOfWork.todo).first() NotImplementedError: Operator 'contains' is not supported on this expression 

Para esta respuesta, asumiré que cls.cases_identified , cls.cases_completed y cls.cases_double_check son de tipo postgresql.ARRAY(Integer) en el lado de Python.

Tu @todo.expression debería devolver solo eso: una expresión SQL. Actualmente está intentando devolver una lista de python. La excepción se produce porque un postgresql.ARRAY no admite el operador in , aunque tiene un método que contains , que se asigna al operador @> en Postgresql y prueba “si los elementos son un superconjunto de los elementos de la expresión de matriz de argumento” . Esto por otro lado no es lo que quieres. Tienes suerte de tener el if x not in ... ahí, tan simple

 [x for x in cls.cases_identified] 

Parece dar lugar a un bucle infinito en lugar de una excepción.

Obtener la diferencia entre arreglos en Postgresql ha sido cubierto aquí extensamente, pero aquí es cómo aplicaría eso usando SQLAlchemy, primero usando un constructor de arreglos :

 from sqlalchemy import func ... class UnitOfWork(...): @todo.expression def todo(cls): # Force correlation of `cls` from outer FROM objects. Use # the `select()` shorthand method of `FunctionElement`. identified = func.unnest(cls.cases_identified).select().correlate(cls) completed = func.unnest(cls.cases_completed).select().correlate(cls) double_check = func.unnest(cls.cases_double_check).select().correlate(cls) # Create the subquery statement stmt = identified.except_(completed.union(double_check)) # Uses the `func` generic for ARRAY constructor from # subquery syntax. The subquery statement has to be # converted to a "scalar subquery" in the eyes of SQLA # (really just a subquery returning 1 col) in order # to remove it from the FROM clause hierarchy. return func.array(stmt.as_scalar()) 

Esto tiene un inconveniente de no proporcionar ningún objeto FROM (ya que se correlaciona todo con la consulta adjunta), por lo que tendría que emitir la consulta original de esta manera:

 test = Session.query(UnitOfWork.todo).select_from(UnitOfWork).first() 

También puede usar el módulo de matriz de Postgresql que proporciona funciones especiales y operadores para matrices de enteros sin nulos:

 class UnitOfWork(...): @todo.expression def todo(cls): return (cls.cases_identified - cls.cases_completed - cls.cases_double_check) 

Tenga en cuenta que primero debe instalar la extensión en Postgresql:

 CREATE EXTENSION intarray;