order_by on model property

[Tim Black]

What is the right way to use .order_by() to order by the values returned by a model object property?  My model object is like this:

class Project(DeclarativeBase):
    __tablename__ = 'project'
    id = Column(Integer, primary_key=True)
    ...
    @property
    def remainderDue(self):
        return self.totalDue - float(self.totalPaid)

The query I'm trying to run is:

projects = DBSession.query(model.Project).order_by(desc(model.Project.remainderDue))

This returns the following error:

Module sqlalchemy.sql.expression:1279 in _literal_as_text
ArgumentError: SQL expression object or string expected.

Tim

[Michael Bayer]

You'd use a hybrid for this case, and due to the usage of float() you'd probably want to produce a separate @expression that doesn't rely on a Python function.

Docs and examples for hybrid are at http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html

Separate @expression:

http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html#defining-expression-behavior-distinct-from-attribute-behavior

The "float()" call in SQL would likely be using CAST, so take a look at http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=cast#sqlalchemy.sql.expression.cast for that.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Tim Black]

Ok, because the totalDue and totalPaid attributes are also SQLAlchemy
declarative model object properties, I converted them (and all other
similar property dependencies) to hybrid_properties and created the
associated @[property].expression methods for every hybrid_property
(though I think those @[property].expression methods are not needed
where SQLAlchemy is not converting a Python method like float() into an
SQL function--I'll test for that later). Now I get this error:

Module projects.model.main:189 in totalDue
>> return sum([w.totalDue for w in self.workDone]) +
cast(self.itemsPurchasedTotal, Float)
TypeError: 'InstrumentedAttribute' object is not iterable

By writing line 189 on several lines, it's apparent self.workDone causes
the error. self.workDone is a relation:

class Project(DeclarativeBase):
# ...
workDone = relation('WorkDone') # one-to-many
# ...
@totalDue.expression
def totalDue(self):
'''Allow this property to be accessed at the class level'''
return sum([w.totalDue for w in self.workDone]) +
cast(self.itemsPurchasedTotal, Float)

Do I need to convert that relation into a hybrid_property, or do
something else in order to use it in this order_by query? I'm beginning
to wonder if it's easier to deal with sorting by @properties by sorting
in Python after running the query--is that the case?

Tim

[Michael Bayer]

When you're inside of @expression, everything you're doing is towards the goal of producing a SQL expression construct that generates a string, which is sent over the wire to the database where it's evaluated as part of a SQL string. So you can't use any Python expressions that aren't supportable as a SQL string, which includes list comprehensions and the sum() function. In this specific case your query likely needs to join() out to a subquery that calculates the sum using the SQL SUM function. The doc at http://www.sqlalchemy.org/docs/orm/tutorial.html#using-subqueries shows the general idea.

> I'm beginning
> to wonder if it's easier to deal with sorting by @properties by sorting
> in Python after running the query--is that the case?

it depends very much on the specifics. If these are records that are already to be loaded in memory, and you're dealing with small collections, then sure. If its a huge report you're doing across thousands of rows, then maybe not.