在基于 Android XML View 重写 Jetpack Compose 版保质期管理 app 时，
用 SQLDelight 替代了 Room 来做 SQLite 数据库管理。
但是在实现过期时间排序功能时，引入了一个 bug。
原来的数据库中的过期时间字段有两种值，NULL 或者时间对应的秒数。
现在又引入了零值。导致排序混乱。
正确的排序效果
在保质期管理 app 中，已过期的物品，或者快过期的应该排列在前面，而不会过期的应该排在后面，如图所示：
零值是否合理
例如录入一个物品时，可以不填写过期时间，那么数据库中应该存储 null 还是 0。
这里确实应该存储 NULL，而不是 0。因为 0 也对应一个日期时间，只有 NULL 才代表未填写。
确保默认值是 NULL 而不是 0
简单粗暴的做法，在保存时，加了一层判断：
var expirationDate: Long?
expirationDate = viewModel.expiredDateMilliSeconds / 1000
if (expirationDate == 0L) {
expirationDate = null
}
NULL 值排序，方法一
如果想将 NULL 值排在后面。之前用 Room 的写法是：
// 过期时间为空的排在最后
@Query("SELECT * from items order by CASE WHEN expirationDate IS NULL THEN 1 ELSE 0 END, expirationDate")
fun getAll(): LiveData<List<Item>>
但是使用 CASE WHEN 这个语法，在 SQLDelight 中会报错
compound operator real, join operator real, GROUP, INDEXED, LIMIT, NOT, ORDER or WHERE expected, got ‘by’
NULL 值排序，方法二 (最终使用) 🍎
SELECT * FROM items ORDER BY COALESCE(expirationDate, 4102444800) ASC, name ASC
- 时间戳格式示例：1685232000.
- 日期 2999-12-31 对应的时间戳为：4102444800
可以使用 COALESCE 函数代替 CASE WHEN 语句，来实现相同的排序效果。
COALESCE 函数返回列表中第一个非空值，因此可以用它来判断 expirationDate 是否为 NULL，如果为 NULL 则返回 1（表示未过期），否则返回 0（表示已过期）。
在这个查询语句中，使用了 COALESCE 函数将 NULL 值替换为一个足够大的日期值（例如 ‘2999-12-31’），以使其在排序时排在未过期的记录之后。
COALESCE 示例：
The COALESCE function accepts two or more arguments and returns the first non-null argument.
SELECT COALESCE(10,20); -- return 10
SELECT COALESCE(NULL,20,10); -- returns 20
最终我是选择了使用 COALESCE 来实现
NULL 值排序，方法三
ORDER BY IFNULL(SOMECOL, 4102444800)
The ifnull() function returns a copy of its first non-NULL argument, or NULL if both arguments are NULL. Ifnull() must have exactly 2 arguments. The ifnull() function is equivalent to coalesce() with two arguments.
注意 IFNULL 是 SQLite 的特有函数，COALESCE 是标准 SQL。
NULL 值排序，方法四
需要 SQLite 3.30.0+ 版本以上：
ORDER BY SOMECOL ASC NULLS LAST;
- SQLDelight 1.x 版本支持 SQLite 3.7.11 至 3.28.0 版本。
- SQLDelight 1.4.2 版本支持 SQLite 3.32.0 及以上版本。
- SQLDelight 1.5.0-beta1 版本开始支持 SQLite 3.34.0 及以上版本。
再看 ROOM
看来 Room 这类 ORM 还是有优势的
- 可以规避一些我的低级错误。
- 似乎对 SQL 支持的更好，至少不会出现 SQLDelight 出现语法不支持的情况
参考
- https://stackoverflow.com/questions/12503120/how-to-do-nulls-last-in-sqlite
关于作者 🌱
我是来自山东烟台的一名开发者，有感兴趣的话题，或者软件开发需求，欢迎加微信 zhongwei 聊聊，或者关注我的个人公众号“大象工具”， 查看更多联系方式