Can you do a CASE WHEN Order_by on a query?

Anvil Q&A
1

What I’m trying to do:
A Custom Sort by Case

What I’ve tried and what’s not working:
Followed the Postgresql example of Order by CASE WHEN

Code Sample:

@anvil.server.callable
def get_all_policies_for_user():
  user = anvil.users.get_user(allow_remembered=True)
  policystatus = 'PolicyStatus' 
  return app_tables.policies.search(
        tables.order_by(  CASE WHEN(policystatus = 'In Progress') THEN 1
                          WHEN(policystatus = 'Active') THEN 2
                          ELSE 4)
        tables.order_by( "PolicyStartDate", ascending=True),   
        User=user )

I’m getting a bad syntax error on this line, so I am guessing you can’t do CASE WHEN order_by, but can anyone confirm this to save me wasting time on it, if that is the case.

tables.order_by(  CASE WHEN(policystatus = 'In Progress') THEN 1
2

I’ve already answered this one: list comprehension.

1 Like
3

Thanks for this, I will search for that answer now.

4

It was when I said “if it takes longer that 30 second to understand this…”

If you spend the time to learn python before using it, you it would save yourself a lot of time. If would be faster than asking how to do things, then copying the code from the form without understanding why it works.

If you don’t learn python, you will keep spending hours creating checkboxes instead of 5 seconds setting their value, or ask questions like this one.

1 Like
5

The docs cover this: Anvil Docs | Using Data Tables from Python They show examples of what you can pass to tables.order_by, and none of them involve anything more than a column name and an options ascending parameter.

If you can’t get the sorting you want through tables.order_by as it stands, then as Stefano says you’re looking at doing it in Python. Personally, I’ve never had a sorting situation that wasn’t covered by tables.order_by.

2 Likes