I have a table Fulfillment with two columns, status and creation date.
I want to order the output of the data from newest to oldest if value of ‘status’ is ‘open’ and oldest to newest if value of ‘status’ is ‘closed’. All records with open statuses should be placed above order with closed statuses.
I am using SQLAlchemy as my ORM with flask. Currently, I have the following query partially doing what I want it to do.
fulfillments = Fulfillments.query.order_by(Fulfillments.status).order_by( Fulfillments.creation_date.desc()).paginate( page=1, per_page=10)
The query orders all records from newest to oldest while placing open statuses above the closed statuses, but does not give us the option of changing the order based on output from the first order by statement.
How should this be implemented?
This ought to work:
fulfillments = Fulfillments.query.order_by(Fulfillments.status.desc()).order_by( db.case( (Fulfillments.status == 'open', db.func.to_days(Fulfillments.creation_date), else_=-(db.func.to_days(Fulfillments.creation_date) ) ).paginate(page=1, per_page=10)
creation_date to a number of days when can then use the
CASE statement to emit the number of days as either a negative or positive integer, based on the value of
status. This will provide the required date order without having to use
Answered By – snakecharmerb