CODEWITHSUNDEEP
sql-order-byweb2py
user2667066
user2667066

Reputation: 2109

web2py logic in orderby clause

I am performing a db query in web2py using the DAL, and I want to use the equivalent of an SQL orderby clause like this:

... ORDER BY table1.col1 = 'my text' DESC, table2.col2 DESC;

But I can't seem to work out the syntax for providing logic in the orderby clause. E.g. the following is not valid syntax

db().select(..., orderby =~ (db.table1.col1=='my text')| db.table2.col2))

I could provide the orderby clause as an SQL string, but then I would need to ensure that 'my text' is correctly SQL-escaped (the actual string is passed in by the user). So if there is no way to put logic in the web2py DAL select(orderby=...) clause, then is there a way to escape SQL strings for use in the orderby clause as an SQL statement?

Upvotes: 0

Views: 818

Answers (1)

Anthony
Anthony

Reputation: 25536

You could try something like this:

orderby = [
    (db.table1.col1 == user_input, 'DESC'),
    (db.table2.col2, 'ASC')
]

db().select(..., orderby=', '.join('%s %s' % level for level in orderby))

With this method, you must manually specify 'DESC' and 'ASC' rather than using the ~ operator to indicate a descending order. If desired, you could write a function to take your original syntax and convert it to something like the above.

Upvotes: 3

Related Questions