Make new order by inspection system support hybrid properties, column properties and synonyms

[Konsta Vesterinen]

First of all I love the new order by inspection (thanks Mike!). It works like a charm for column properties however I noticed that it doesn't work with hybrids. This test case illustrates the problem:

import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property


engine = sa.create_engine('sqlite:///:memory:')
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()


class User(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)

    first_name = sa.Column(sa.Unicode(255))
    last_name = sa.Column(sa.Unicode(255))

    @hybrid_property
    def name(self):
        """User's full name."""
        return u'%s %s' % (self.first_name, self.last_name)

    @name.expression
    def name(self):
        return sa.func.concat(self.first_name, ' ', self.last_name)


print session.query(User).order_by('name')

print session.query(User).order_by(User.name)

First query returns warning whereas I think it should return the same result as the latter query.

Investigated this a little further and it seems support for synonyms and column properties is lacking as well. The following snippet illustrates this:

import sqlalchemy as sa
from sqlalchemy.orm import column_property, sessionmaker
from sqlalchemy.ext.declarative import declarative_base, synonym_for
from sqlalchemy.ext.hybrid import hybrid_property


engine = sa.create_engine('sqlite:///:memory:')
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()


class User(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)

    first_name = sa.Column(sa.Unicode(255))

    @synonym_for
    @property
    def name(self):
        return self.first_name

    name_lower = column_property(sa.func.lower(first_name))


print session.query(User).order_by('name')

print session.query(User).order_by(User.name)

print session.query(User).order_by('name_lower')

print session.query(User).order_by(User.name_lower)

[Mike Bayer]

two things are going on here. One is that the whole order_by("name") thing requires that the column or label you intend to order by is already present in the SELECT list. so that eliminates hybrids as a possibility, because a hybrid is not part of the SELECT unless explicitly stated. The other is that order_by("name") is a Core feature, not ORM, and only selects for that "name" actually present. So if your hybrid were included in the query, and you labeled it "name", or if your column property labeled itself, it would work:

class User(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)

    first_name = sa.Column(sa.Unicode(255))
    last_name = sa.Column(sa.Unicode(255))

    @hybrid_property
    def name(self):
        return self.first_name + " " + self.last_name

    name_lower = column_property(sa.func.lower(first_name).label("name_lower"))


print session.query(User.name.label('name')).order_by('name')

print session.query(User).order_by("name_lower")

And it can't work for synonyms because again synonyms have nothing to do with the generation of the columns clause of the SELECT.

order_by("name") is not intended as a means of a shortcut of specifying Entity.name; that would not work if the query had multiple entities each with an expression named "name" in any case. It is a SQL-level optimization that specifies that for a SELECT which already has a specific label "name" in the columns clause, that the ORDER BY should re-state the expression using just the label name, rather than re-writing the whole expression out a second time.

I guess it looks like a shortcut to query(User).order_by(User.name), where "name" is a regular mapped column, because it is locating a column that happens to be named "name".

[Konsta Vesterinen]

Ok thanks for clarifying this. What you said makes perfect sense and I agree this functionality shouldn't then be added to hybrid / synonym expressions.