'order_by' in __mapper_args__ breaks queries that use union().

[Christophe Biocca]

I attached a reproduction. On Postgres it manifests as a different error, but the causes are the same.

We're working around it by not using order_by in mapper args, and instead setting it manually on the relevant queries.

[Mike Bayer]

to be honest I'd rather just deprecate the "order_by" param on mapper and I'm surprised it isn't already.

[Christophe Biocca]

Thanks. When a model needs ordering in general, what's the recommended alternative? Just add it to the queries?

[Mike Bayer]

the problem with "order by" stuck on mapper is that it is ambiguous how/when this order by should be used, and when it should not. Such as if you selected from a bunch of models at the same time, in which order should the final result be ordered? I think right now it might just be using "the first" mapper for the order by and ignoring the rest, I'm not even sure. Any feature that has to pick and choose based on an undocumented and hard-to-follow heuristic based on when it happens to do something and when it doesn't doesnt really belong in the core featureset.

What there is a growing need for is more mechanisms by which people want to alter queries on an automated basis. We already have simple things like the PreFilteredQuery (https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PreFilteredQuery), and in this case you might want to adapt this for the order_by, but working in openstack there is a need for something a lot more flexible. it really suggests the need for something big, like a new kind of meta language based on the contents of an ORM query object.

[Mike Bayer]

see #3394; the feature will be indefinitely deprecated but it's not worth trying to make it work in more complex use cases like this one.

[Mike Bayer]

also as far as that meta language thing, that is a more long term idea discussed in https://bitbucket.org/zzzeek/sqlalchemy/issue/3225/query-heuristic-inspection-system. the mentioned before_compile() event is present in 1.0.