代码之家 › 专栏 › 技术社区 › Zack Herbert

按案例排序条款的范围工作不正常

named-scope ruby-on-rails mysql

Zack Herbert · 技术社区 · 7 年前

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

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

4 回复 | 直到 7 年前

Darshan Mehta 7 年前

你能试试吗 ISNULL 相反,例如:

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

cwallenpoole 7 年前

没有办法将所有这些条件都放入 CASE... WHEN CASE ... WHEN WHERE 条款因此,你所做的与:

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

您真正想要的是一系列列值@Darshan有一种方法,基本上是创建一系列布尔列并将其添加到排序中:

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,

您还可以选择将列值默认为MySQL最大值。在这种情况下 '9999-12-31' 代表 highest possible date 和 ~0 represents the max int value :

complete, 
   ISNULL(sort_index, ~0), 
   ISNULL(scheduled_end_time, '9999-12-31'), 
   ISNULL(starts_at, '9999-12-31')

complete 如果 sort_index 不是null”,并且所有其他列遵循相同的逻辑。

David Aldridge 7 年前

order by complete,
         Coalesce(
           sort_index,
           scheduled_end_time,
           starts_at,
           #{now} + INTERVAL 10 YEAR
         )

然而,在这种排序中,您将整数与日期进行比较,因此我不确定这将如何工作——可能是通过隐式类型转换,这不太可能导致期望的结果。

order by complete,
         sort_index,
         Coalesce(
           scheduled_end_time,
           starts_at,
           #{now} + INTERVAL 10 YEAR
         )

Wizard of Ogz 7 年前

-value 戏法我和其他人一样,也添加了合并,这确实清理了案例陈述。

ORDER BY 
  complete, 
  -sort_index DESC, -- Sort ascending with nulls last
  COALESCE(scheduled_end_time, starts_at, now() + INTERVAL 10 YEAR)
;

sqlfiddle