I have a scope on my rails model that is supposed to help me sort my objects. This is shown below:
scope :active, ->(u = nil, now = "NOW()") {
published_and_private(u).eager_load(:images)
.where("(listing = 1 AND complete = 0) OR " + # LISTING
"(online_only = 1 AND scheduled_end_time + INTERVAL 1 DAY >= #{now}) OR " + # TIMED
"(online_only = 0 AND listing = 0 AND starts_at + INTERVAL 1 DAY >= #{now})") # LIVE
.order("complete, CASE WHEN sort_index IS NOT NULL THEN sort_index " +
"WHEN scheduled_end_time IS NOT NULL THEN scheduled_end_time " +
"WHEN starts_at IS NOT NULL THEN starts_at ELSE #{now} + INTERVAL 10 YEAR END")
}
Below is the data from the database that is returned when the query gets ran:
select id, name, complete, sort_index, starts_at, scheduled_end_time from auctions where published = 1 ORDER BY complete, CASE WHEN sort_index IS not NULL THEN sort_index WHEN scheduled_end_time IS NOT NULL THEN scheduled_end_time WHEN starts_at IS NOT NULL THEN starts_at ELSE (NOW() + INTERVAL 10 YEAR) END;
+----+-----------------------------------+----------+------------+---------------------+---------------------+
| id | name | complete | sort_index | starts_at | scheduled_end_time |
+----+-----------------------------------+----------+------------+---------------------+---------------------+
| 21 | Listing: Mountain Cabin Estate | 0 | 1 | NULL | NULL |
| 17 | Multi-Item Online Only | 0 | 2 | 2017-08-07 06:48:00 | 2017-08-21 12:48:00 |
| 9 | Multi-item Live Auction | 0 | NULL | 2017-08-21 18:48:02 | NULL |
| 19 | Many Item LIVE Auction | 0 | NULL | 2017-08-21 18:48:02 | NULL |
| 10 | Single Item Online Only | 0 | NULL | 2017-08-07 18:48:03 | 2017-08-22 00:48:02 |
| 18 | MANY Item Timed Auction | 0 | NULL | 2017-08-07 18:48:03 | 2017-08-22 00:48:02 |
| 22 | LISTING: Multi-parcel Real Estate | 0 | NULL | NULL | NULL |
| 20 | Bad Images | 0 | 3 | 2017-08-21 14:48:00 | NULL |
| 8 | Single Item Live Auction | 1 | NULL | 2017-08-21 18:48:02 | NULL |
+----+-----------------------------------+----------+------------+---------------------+---------------------+
My problem is that the object with a sort index of 3 is out of place, this happens for any number over 2 and I am at a complete loss as to why this may be. I was expecting the query to place that object right under the one with a sort_index of 2.
Any help, guidance, or insight would be greatly appreciated.
There isn't a way to put all of those conditions into a CASE... WHEN. CASE ... WHEN effectively creates one pseudo-column in your WHERE clause. So what you did is no different from:
SELECT *, CASE /* your logic */ END AS sort_logic
/*
Notice that the sort_logic column doesn't actually exist in the table. Instead
MySQL calculates the value and for the duration of this query
*/
WHERE /* <stuff> */
ORDER BY sort_logic
What you really want is a series of column values. @Darshan has one approach above, which is basically create a series of boolean columns and add those to the sort:
complete, ISNULL(sort_index) DESC,
/*
Desc and ASC are important here. You want NOT NULL values to float to
the top, but you want the columns to work in ascending order.
*/
sort_index ASC,
ISNULL(scheduled_end_time) DESC, scheduled_end_time ASC,
ISNULL(starts_at) DESC, starts_at ASC,
You also have the option of defaulting the column values to MySQL max values. In this case '9999-12-31' represents the highest possible date, and ~0 represents the max int value:
complete,
ISNULL(sort_index, ~0),
ISNULL(scheduled_end_time, '9999-12-31'),
ISNULL(starts_at, '9999-12-31')
In this case, the logic is "Sort by complete, if sort_index isn't null use that for sorting, otherwise throw the result to the end of the list where sort_index isn't null…" and the same logic follows for all of the other columns.