Actually, now I see that your suggestion would get me the column object (not a string), but it would still restrict me to the study table.
On Thu, Feb 13, 2014 at 7:53 PM, Tony Garcia <tnyr...@gmail.com> wrote: > Oops -- disregard the [start:end] at the end of the query and replace that > with .all() > > > On Thu, Feb 13, 2014 at 7:50 PM, Tony Garcia <tnyr...@gmail.com> wrote: > >> Hmm.. I see what you're saying, but the column can be from any of the >> tables queried from, not just the Study table. So it could be >> Study.study_id, System.system_name, Site.site_id, etc. Also won't that >> getattr() call just return a string? I was under the impression that you >> had to pass a column object to order_by(). So if implemented the solution >> for dynamic sort direction given in the stackoverflow link above (which >> takes advantage of the fact that you can access the .asc() or .desc() methods >> as attributes on the column object), I have this: >> >> def my_query(sort_direction='asc'): >> column_sorted = getattr(Study.study_id, sort_direction)() >> >> query = Study.query.options( >> db.joinedload(Study.system), >> db.joinedload(Study.site)).\ >> filter(System.system_id==41).\ >> order_by(column_sorted)[start:end] >> return query >> >> How can I modify this so that it doesn't just sort on Study.study_id and >> my method signature would be my_query(sort_column, sort_direction)? >> Maybe this isn't possible using the ORM and I have to dip down into the >> SQL expression language, but I thought I'd ask. >> >> Thanks. >> >> >> On Thu, Feb 13, 2014 at 7:08 PM, Michael Bayer >> <mike...@zzzcomputing.com>wrote: >> >>> >>> On Feb 13, 2014, at 6:21 PM, Tony Garcia <tnyr...@gmail.com> wrote: >>> >>> > Hello, >>> > I'm new to SQLAlchemy and have searched high and low for a solution to >>> my problem so I'm hoping someone here can help. I have a query where I need >>> to apply the 'order by' clause dynamically (both the column and the >>> direction). So a 'static' version of my query would be: >>> > >>> > studies = session.query(Study).options( >>> > joinedload(Study.system), >>> > joinedload(Study.site)). >>> > filter(System.system_id=41). >>> > order_by(Study.study_id.desc()). >>> > all() >>> > >>> > However the order can be asc or desc and it could be any column from >>> the 3 tables. I found this post on Stackoverflow which helps with a dynamic >>> sort direction (asc, desc), but it doesn't help me with the dynamic column: >>> > >>> > >>> http://stackoverflow.com/questions/20904226/python-sqlalchemy-dynamic-order-by >>> >>> "dynamic" attribute access in Python is using the getattr() builtin >>> function: >>> >>> def my_query(order_by_column): >>> >>> query = >>> session.query(Study).filter(Study.system_id=41).order_by(getattr(Study, >>> order_by_column)) >>> >>> that seems like what you're asking, hope it helps. >>> >>> >>> >> > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
