Dynamic order by clause

[Tony Garcia]

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

Thanks in advance.

-Tony

[Michael Bayer]

“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.

[Tony Garcia]

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.

[Tony Garcia]

Oops -- disregard the [start:end] at the end of the query and replace that with .all()

[Tony Garcia]

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.

[Michael Bayer]

everything in python is ultimately in a namespace, the names are strings, the values are the objects.   

like if you had “myapp.model” as a module, and in that module were Study and Site, you could say:

from myapp import model

Study = getattr(model, “Study”)

same thing.

If you want to poke into the registry of class names in declarative, you can look inside of Base._decl_class_registry:

def query(clsname, colname):

    cls = Base._decl_class_registry[clsname]

    col = getattr(cls, colname)

   

   q = query(cls).filtert(cls.foo == ‘bar’).order_by(col)

this kind of thing is very easy in Python once you get the central idea that everything in Python is the same kind of object each with a name.

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[Tony Garcia]

Gotcha. Thanks Michael. Once I get the code working I'll post it here. Off to bed now, though.

Cheers,
Tony

[Josh Kuhn]

I don't know if this is what you're thinking, but you can also just build a query object in different ways if you want to

query = session.query(Study).options(

                                     joinedload(Study.system),

                                     joinedload(Study.site)).

                                     filter(System.system_id=41)

if order_by_study_id:

    if descending:

        query = query.order_by(Study.study_id.desc())

    else:

        query = query.order_by(Study.study_id)

... # whatever other branches etc.

final_results = query.all()