Sort ActiveRecord collection by different attributes depending on a third attribute

Question

I have an Order model with those attributes:

What I am trying to do, is to sort two collections by created_at or delivery_at depending on its status.

On the model, I created a method which checks the status order and return created_at or delivery_at datetime.

def status_date
  if status == 'on_delivery'
    delivered_at.to_datetime
  else
    created_at.to_datetime
  end
end

And then in the controller:

created = Order.where(status: 'open')
on_delivery = Order.where(stauts: 'on_delivery')

orders = created + on_delivery
orders.sort_by(&:status_date)

Which is not working.

I am trying to achieve a chronological list of orders, but DateTime should be related to its status.

score 1 · Accepted Answer · answered Oct 23 '18 at 17:50

1

You can do it all in the database by querying both statuses at once and then using an SQL CASE expression for the ordering:

case status
when 'open' then created_at -- if the status is open then use created_at
else delivered_at           -- otherwise use delivered_at
end

You only have two statuses so those branches are sufficient.

Putting it together:

orders = Order.where(status: %w[open on_delivery])
              .order(Arel.sql("case status when 'open' then created_at else delivered_at end"))

The Arel.sql is needed to get an SQL string past #order without complaint in Rails5 (and will be mandatory in Rails6).

answered Oct 23 '18 at 17:50

mu is too short

I tried it, but it's not working. The most simple query I can build is: `Order.by_date(date).where(status: %w[open on_delivery]).order(Arel.sql("CASE status WHEN 'open' THEN created_at ELSE delivered_at END"))`. The by_date it's just a scope to fetch from beginning to end of given date. Anyway, the query returns me the orders well, but the order seemed to be grouped by its status (one date I got on_delivery-open-on_delivery grouped result, other open-on_delivery), and there isn't any grouping. – pzin Oct 27 '18 at 20:39
That "most simple" query is the same as mine but you've introduced the `by_date` scope. The order is by timestamp but the timestamp used depends on the `status` which is what you're looking for, right? I don't know what you mean "there isn't any grouping". – mu is too short Oct 27 '18 at 22:26
Aah. The problem was other dates coming up, so I did: `Arel.sql("case status when 'open' then created_at::time else delivered_at::time end")` – pzin Oct 28 '18 at 08:30

1 Answers1