CODEWITHSUNDEEP
pythonsqlsqlalchemy
Zitrax
Zitrax

Reputation: 20255

hybrid property with join in sqlalchemy

I have probably not grasped the use of @hybrid_property fully. But what I try to do is to make it easy to access a calculated value based on a column in another table and thus a join is required.

So what I have is something like this (which works but is awkward and feels wrong):

class Item():

:

@hybrid_property
def days_ago(self):
    # Can I even write a python version of this ?
    pass

@days_ago.expression
def days_ago(cls):
    return func.datediff(func.NOW(), func.MAX(Event.date_started))

This requires me to add the join on the Action table by the caller when I need to use the days_ago property. Is the hybrid_property even the correct approach to simplifying my queries where I need to get hold of the days_ago value ?

Upvotes: 5

Views: 9119

Answers (1)

zzzeek
zzzeek

Reputation: 75137

One way or another you need to load or access Action rows either via join or via lazy load (note here it's not clear what Event vs. Action is, I'm assuming you have just Item.actions -> Action).

The non-"expression" version of days_ago intends to function against Action objects that are relevant only to the current instance. Normally within a hybrid, this means just iterating through Item.actions and performing the operation in Python against loaded Action objects. Though in this case you're looking for a simple aggregate you could instead opt to run a query, but again it would be local to self so this is like object_session(self).query(func.datediff(...)).select_from(Action).with_parent(self).scalar().

The expression version of the hybrid when formed against another table typically requires that the query in which it is used already have the correct FROM clauses set up, so it would look like session.query(Item).join(Item.actions).filter(Item.days_ago == xyz). This is explained at Join-Dependent Relationship Hybrid.

your expression here might be better produced as a column_property, if you can afford using a correlated subquery. See that at http://docs.sqlalchemy.org/en/latest/orm/mapping_columns.html#using-column-property-for-column-level-options.

Upvotes: 9

Related Questions