ordering from query and orm relation conflict

[Former user]

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.

[Mike Bayer]

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 other DesktopStatus objects besides those which are associated with the parent Desktop ? I don't see why order_by() should get special treatment.

If you need special per-query ordering on the relation(), why can't you:

• set order_by=None on the original relation, so that order_by() is not defined at the base
• use a distinct query - query(DesktopStatus).with_parent(mydesktop).order_by(whatever) ?

The 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-hoc order_by does (which is why it starts relating to #1079).

[Michael Trier]

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.

[Mike Bayer]

#1079 is accepted and this is a dupe.

[Mike Bayer]

Removing milestone: 0.5.xx (automated comment)