- edited description
Order by inspection sometimes returns wrong expression for labeled expressions
The following simplified code 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)
name = sa.Column(sa.String)
class Organization(Base):
__tablename__ = 'organization'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String)
class OrganizationMember(Base):
__tablename__ = 'organization_member'
user_id = sa.Column(sa.Integer, sa.ForeignKey('user.id'), primary_key=True)
organization_id = sa.Column(
sa.Integer,
sa.ForeignKey('organization.id'),
primary_key=True
)
is_admin = True
user = sa.orm.relationship(User, backref='memberships')
organization = sa.orm.relationship(Organization)
@hybrid_property
def id(self):
return '{0}:{1}'.format(self.organization_id, self.user_id)
@id.expression
def id(cls):
return sa.func.concat(
cls.organization_id,
sa.text("':'"),
cls.user_id
).label('id')
sa.orm.configure_mappers()
print(
session.query(User.id)
.join(User.memberships)
.order_by(OrganizationMember.id)
)
# SELECT user.id AS user_id
# FROM user JOIN organization_member ON user.id = organization_member.user_id
# ORDER BY id
print(session.query(User.id).order_by(User.name.label('id')))
# SELECT user.id AS user_id FROM user ORDER BY id
Related to: https://github.com/kvesteri/sqlalchemy-json-api/issues/10
Comments (6)
-
reporter -
repo owner the behavior seems consistent between both examples, you're giving it a label, therefore it assumes you have named that expression elsewhere to be ordered by. If you call .element on each, you get the SQL expression. It's not feasable that it tries to guess if the "id" label is actually what you want to render, or if it should go looking to see that the labeled expression isn't present therefore it has to render the whole thing. There are some cases where ordering by a label may resolve to the original expression, but that is based on dialect-specific rules for things that can't handle "order by <labelname>". Overall, it is very desireable for a statement to look like "SELECT <complex thing> AS <label> ORDER BY <label>", rather than "SELECT <complex thing> ORDER BY <complex thing again>".
-
repo owner hurm http://docs.sqlalchemy.org/en/latest/changelog/migration_09.html#label-constructs-can-now-render-as-their-name-alone-in-an-order-by "The ORDER BY only renders the label if the label isn’t further embedded into an expression within the ORDER BY", OK, well lets see then
-
repo owner -
repo owner kind of a bigish change for a point release but the existing behavior doesn't seem very useful.
-
repo owner - changed status to resolved
Tighten rules for order_by(Label) resolution
- Fixed bug originally introduced in 0.9 via
1068
where order_by(<some Label()>) would order by the label name based on name alone, that is, even if the labeled expression were not at all the same expression otherwise present, implicitly or explicitly, in the selectable. The logic that orders by label now ensures that the labeled expression is related to the one that resolves to that name before ordering by the label name; additionally, the name has to resolve to an actual label explicit in the expression elsewhere, not just a column name. This logic is carefully kept separate from the order by(textual name) feature that has a slightly different purpose.
Change-Id: I44fc36dab34380cc238c1e79ecbe23f1628d588a Fixes:
#3882→ <<cset 6b489db89970>>
- Log in to comment