Except that LIMIT and OFFSET are present in my query, gnarly isn't it ? :P d = label("distance", some_complicated_geoalchemy_function_call(columns...))
q = session.query(Product, Merchant.location, d)\ .join(Merchant, Product.merchant_id == Merchant.id)\ .filter(Product.numinstock > 0)\ .options(subqueryload_all(Product.origin, Product.style, Product.foods, Product.flavors, Product.occasions, Product.moods, Product.varieties)) q = q.order_by("distance").offset(0).limit(20).all() On Jan 9, 3:57 am, Michael Bayer <mike...@zzzcomputing.com> wrote: > On Jan 8, 2012, at 2:47 PM, Yuen Ho Wong wrote: > > > > > > > > > > > Hi, > > > I have a rather complicated problem and I was wondering if you guys > > could help. > > > So I have a query, session.query(Product, Merchant, d), where Product > > is 1-to-many with Merchant, and d is the distance from some lat long. > > d is actually a sqlalchemy.sql.label() of some complicated GeoAlchemy > > function calls. > > > Product has a number of collections in which I would like to load > > using subqueryload_all() as well, and the result is ordered by > > "distance" as in order_by("distance"), where "distance" is the name of > > the label d. > > > My problem is, since I'm supplying the Query object with an explicit > > order_by() name, when I use subqueryload(), the order_by() name is put > > into the subquery as is, because SQLAlchemy doesn't know any better > > with a plain string. If I pass in a column element, SQLAlchemy seems > > to know not to put an ORDER BY in the subquery. This seems to me like > > a bug because a subqueryload() always join on the primary key of the > > previous SELECT, so unless the name is the primary key name, it really > > shouldn't be put in the subquery. > > > So finally my question, if this is too hard to fix, is there an option > > somewhere that I can tell SA to ignore the previous order_by() when > > doing a subqueryload()? If not, and I can't wait for a fix now, is > > there a way where I can turn my distance label into a ClauseElement so > > that SA knows not to put into the subquery when loading collections? > > any chance you can save me some time and attach a complete, succinct .py > example here ? subqueryload removes the ORDER BY from the query, provided > LIMIT/OFFSET aren't present, unconditionally. It doesn't care that it's a > string or not. > > http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/orm/strategies.... -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.