How to annotate a function element with a FROM object in SQLAlchemy

Question

While answering another question I hit a wall in trying to produce a hybrid property that would return a function expression wrapping a scalar subquery that correlates all its FROM objects from the enclosing query, but it'd still provide a FROM object if no other expression in the enclosing query provides the table to correlate against.

Given a toy model definition

class Foo(Base):
    __tablename__ = 'foo'

    foo_id = Column(Integer, primary_key=True, autoincrement=True)
    bar = Column(postgresql.ARRAY(Integer))
    baz = Column(postgresql.ARRAY(Integer))

    @hybrid_property
    def bar_sans_baz(self):
        return list(set(self.bar).difference(self.baz))

    @bar_sans_baz.expression
    def bar_sans_baz(cls):
        bar = func.unnest(cls.bar).select().correlate(cls)
        baz = func.unnest(cls.baz).select().correlate(cls)
        stmt = bar.except_(baz)
        # Uses `func` generic as ARRAY() constructor
        return func.array(stmt.as_scalar(),
                          type_=postgresql.ARRAY(Integer))

the problem is that

session.query(Foo.bar_sans_baz)

results in

SELECT array((SELECT unnest(foo.bar) AS unnest_1 
              FROM foo EXCEPT SELECT unnest(foo.baz) AS unnest_2 
              FROM foo)) AS bar_sans_baz

since it has nothing to correlate against in the enclosing query. A simple remedy is to use select_from() to explicitly add the FROM object:

session.query(Foo.bar_sans_baz).select_from(Foo)

and the resulting query is the desired

SELECT array((SELECT unnest(foo.bar) AS unnest_1
              EXCEPT SELECT unnest(foo.baz) AS unnest_2)) AS bar_sans_baz 
FROM foo

While this works, it adds mental overhead as you have to remember to add the select_from(Foo), if only selecting the hybrid property. This is not an issue, if any other select item provides Foo:

In [105]: print(session.query(Foo.bar_sans_baz, Foo.foo_id))
SELECT array((SELECT unnest(foo.bar) AS unnest_1 EXCEPT SELECT unnest(foo.baz) AS unnest_2)) AS bar_sans_baz, foo.foo_id AS foo_foo_id 
FROM foo

Is there a way to annotate the returned function expression func.array(...) – or the inner scalar subquery – so that it will provide Foo as the FROM object, if no other expression provides it. Or in other words, just a way to add Foo as from_obj to the function expression or the scalar subquery so that the inner selects can use that.

Answer 1

I am wondering why you won't add .select_from explicitly in the expression definition:

@bar_sans_baz.expression
def bar_sans_baz(cls):
    bar = func.unnest(cls.bar).select().correlate(cls)
    baz = func.unnest(cls.baz).select().correlate(cls)
    stmt = bar.except_(baz).select_from(cls.__tablename__)
    # Uses `func` generic as ARRAY() constructor
    return func.array(stmt.as_scalar(),
                      type_=postgresql.ARRAY(Integer))