postgresql - 在 SQLAlchemy 的 hybrid_property 中使用（加入和选择）Postgres CTE

Question

我有一个 Postgres 函数加载到一个数据库中，它需要两个参数来获取一些数据。我正在尝试使用 SQLAlchemyhybrid_property.expression访问并使其在生成的select.

这是函数的标题：

CREATE OR REPLACE FUNCTION the_func(int, character varying)
RETURNS TABLE (
  mymodel_id int,
  x_const character varying,
  y_const character varying,
  selection_priority int
)
AS ...

在 SQL 中，使用它看起来像这样：

SELECT
  COALESCE(b.x_const, b.y_const) AS constant,
FROM a
  LEFT JOIN the_func(a.id, 'abc') AS b
  ON b.mymodel_id = a.id;

但是，我正在努力将其纳入 SQLAlchemy：

class MyModel:
    id = sa.Column(sa.Int)

    @hybrid_property
    def the_const(self):
        """Python version..."""
        # ???
    
    @the_const.expression
    def the_const(cls):
        """Use the CTE..."""
        # somehow join? 
        # ...join(
        #         aliased(
        #             func.the_func(MyModel.id, 'abc'),
        #             name="const_func"
        #         )
        #    )...
        return select([text("const_func.constant")]).as_scalar()

我认为有可能将 CTE 放入 SQLAlchemy 的 CTE（类似于 OP 在这里的做法：Join on a CTE in SQLAlchemy），但想知道当它们已经加载到数据库中时是否有办法使用它们（如在这个案子）。

标签： postgresqlsqlalchemycommon-table-expression

Answer 1

在添加连接到已经有 select_from() 的 SQL Alchemy 表达式的帮助下，我找到了一个解决方案

class MyModel:
    id = sa.Column(sa.Int)

    @the_const.expression
    def the_const(cls):
        """Use the CTE..."""

        # Alias the CTE:
        const_func = aliased(
            func.the_func(MyModel.id, 'abc'),
            name="b"
        )

        # Set up the join:
        the_join = cls.__table__
        the_join = the_join.join(
            const_func,
            text("b.mymodel_id") == MyModel.id
        )
        # Finally select using the text() wrapper, from the joined tables
        query = select(
            [text("COALESCE(b.x_const, b.y_const)")]
        ).select_from(the_join)
        return query.as_scalar()