mysql - 带有 ORDER BY CASE 子句的范围无法正常工作

Question

我的 Rails 模型上有一个范围，应该可以帮助我对对象进行排序。如下所示:

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

以下是运行查询时返回的数据库数据:

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                |
+----+-----------------------------------+----------+------------+---------------------+---------------------+

我的问题是排序索引为 3 的对象不合适，任何超过 2 的数字都会发生这种情况，我完全不知道为什么会出现这种情况。我期望查询将该对象放置在 sort_index 为 2 的对象的正下方。

任何帮助、指导或见解将不胜感激。

Answer 1

您可以尝试使用 ISNULL 来代替，例如:

.order("complete, ISNULL(sort_index), sort_index, " +
         "ISNULL(scheduled_end_time), scheduled_end_time " +
         "ISNULL(starts_at), starts_at")