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 <[email protected]> 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 > <[email protected]>wrote: > >> >> On Feb 13, 2014, at 6:21 PM, Tony Garcia <[email protected]> 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
