SQLAlchemy: Ordering data based on the output of another order by statement-CodePudding

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?

CodePudding user response：

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)

By converting 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 ASC or DESC.