Zack Herbert Zack Herbert - 4 months ago 20
MySQL Question

How to use an order_by clause on a rails scope that handles null values

I am trying to order some values that I get back from a query. The scope is as follows:

scope :complete, -> {where(status:[Status::SOLD, Status::NO_SALE])}


In an API I return
a.items.complete
which is call the above query. I need to modify this call by adding an
order
clause based on two attributes:
starts_at
and
scheduled_end_time
in descending order. So I tried the following but and getting an error:

items = items.complete.order("CASE WHEN items.actual_end_time IS NULL THEN items.starts_at ELSE items.scheduled_end_time END DESC")


ERROR:
{"error":"Mysql2::Error: Unknown column 'items.starts_at' in 'order clause': SELECT `items`.* FROM `items` WHERE `items`.`company_id` = 2 AND `items`.`status` IN ('sold', 'no_sale') ORDER BY CASE WHEN scheduled_end_time IS NULL THEN starts_at ELSE scheduled_end_time END DESC LIMIT 25 OFFSET 0"}


I am trying to determine how to check if scheduled_end_time is null and if so order by the starts_at...If I strip of the DESC at the end it works, but I need them to be descending. Any help or pointers is appreciated.

Auctions have many Items for what its worth.

Auctions schema:

enter image description here

Items schema:

enter image description here

Answer

Refer to the order fields by their full names:

items = items.complete.order("CASE WHEN items.scheduled_end_time IS NULL THEN items.starts_at ELSE items.scheduled_end_time END DESC")

UPDATE

You could use COALESCE and multiple order clauses to get there. Also, you need to join the auctions. Something along these lines:

items.complete.
    joins(:auctions).
    order("COALESCE(auctions.scheduled_end_time, date('01-01-1970')) DESC").
    order("COALESCE(auctions.starts_at, date('01-01-1970')) DESC")

COALESCE replace NULL's with it's first non-null argument so the field is treated like having a value during the ordering. Depending on whether you want NULLs on bottom or on top, pass a value beyond the lowest or highest range boundary of your data, respectively.

I'm not sure whether MySQL supports the date('01-01-1970') syntax, maybe it's something like CAST('01-01-1970' AS DATETIME), but the approach should be valid.

Another, cleaner approach would be to explicitly sort each field by NULL first:

items.complete.
    joins(:auctions).
    order("auctions.scheduled_end_time IS NULL, auctions.scheduled_end_time DESC").
    order("auctions.starts_at IS NULL, auctions.starts_at DESC")
Comments