ordering from query and orm relation conflict
If you define a default ordering in a relation and do a query on that relation using a different ordering the result is not what you would expect. In my code I define a mapper like this:
orm.mapper(Desktop, desktop_table, properties=dict(
status_history = orm.relation(DesktopStatus,
backref="desktop",
order_by=DesktopStatus.updated,
lazy="dynamic"),
))
In one method of the Desktop class I need use the last entry in that list. A slice results in all entries being fixed, so I did this:
status=self.status_history.order_by(DesktopStatus.updated.desc()).limit(1).first().updated
which resulted in this SQL query:
10:55:38,427 INFO [sqlalchemy.engine.base.Engine.0x..ec](sqlalchemy.engine.base.Engine.0x..ec) SELECT desktop_status.id AS desktop_status_id, desktop_status.desktop_id AS desktop_status_desktop_id, desktop_status.old_status AS desktop_status_old_status, desktop_status.new_status AS desktop_status_new_status, desktop_status.updated AS desktop_status_updated
FROM desktop_status
WHERE %(param_1)s = desktop_status.desktop_id ORDER BY desktop_status.updated, desktop_status.updated DESC
LIMIT 1 OFFSET 0
notice how desktop_status.updated appears twice in the ORDER BY clause. The first one takes precedence, so the explicit ordering from my query is ignored.
This is somewhat related to #1079. I felt that this specific issue is different enough to warrant a separate bugreport.
Comments (6)
-
repo owner -
repo owner - marked as enhancement
-
I think a decision needs to made with respect to this issue for this ticket and
#1079. Although I can kind of see the argument for allowing default ordering in a relation and then wanting to override it, it sounds like that really opens up the options to a level of overriding possibility that makes no sense.Personally I'm +0 on allowing order_by() or order_by(None) for the relation situation and default ordering in a mapper.
-
-
repo owner - changed status to duplicate
#1079is accepted and this is a dupe. -
repo owner - removed milestone
Removing milestone: 0.5.xx (automated comment)
- Log in to comment
Just like my comment on
#1079, I don't yet see the rationale here. Would you also want to cancel out the filtering on the query such that you select otherDesktopStatus
objects besides those which are associated with the parentDesktop
? I don't see whyorder_by()
should get special treatment.If you need special per-query ordering on the
relation()
, why can't you:order_by=None
on the original relation, so thatorder_by()
is not defined at the baseThe use case for
dynamic
is that the end-collection is very large and you'd like to get a filtered view of it - further sub-filtering doesn't change the definition of the collection, but an ad-hocorder_by
does (which is why it starts relating to#1079).