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.

sophros · Accepted Answer · 2018-11-20 17:16:17Z

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))

answered Nov 20, 2018 at 17:16

sophros

17.3k12 gold badges52 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ilja Everilä Over a year ago

It's a good idea, though a CompoundSelect such as produced by except_() does not have the method select_from(). I'm also not entirely sure how it'd play out, if you add "select from" to the statement that will then be marked scalar subquery and wrapped in ARRAY() constructor.

Ilja Everilä Over a year ago

In case the previous comment wasn't clear (it got a bit lengthy), this does not work.

sophros Over a year ago

Thank you for your comments. I was not sure it would 100% work. Maybe you could try to improve my answer?

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related