CODEWITHSUNDEEP
mysqlruby-on-rails
Zack Herbert
Zack Herbert

Reputation: 960

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

Upvotes: 0

Views: 82

Answers (2)

Nic Nilov
Nic Nilov

Reputation: 5155

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")

Upvotes: 1

Pragash
Pragash

Reputation: 743

Remove the , before the DESC keyword. The SQL interpreter is not able to interpret this properly due to the existence of the ,. Once you remove that it should work properly.

Upvotes: 1

Related Questions