How to use an order_by clause on a rails scope that handles null values
<p>I am trying to order some values that I get back from a query. The scope is as follows: </p>

<p><code>scope :complete, -&gt; {where(status:[Status::SOLD, Status::NO_SALE])}</code></p>

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

<p><code>items = items.complete.order("CASE WHEN items.actual_end_time IS NULL THEN items.starts_at ELSE items.scheduled_end_time END DESC")</code></p>

<p><strong>ERROR:</strong>
<code>{"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"}</code></p>

<p>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.</p>

<p>Auctions have many Items for what its worth.</p>

<p><strong>Auctions schema:</strong></p>

<p><a href="https://i.stack.imgur.com/lPadX.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/lPadX.png" alt="enter image description here"></a></p>

<p><strong>Items schema:</strong></p>

<p><a href="https://i.stack.imgur.com/nuoz9.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nuoz9.png" alt="enter image description here"></a></p>