Android Room Dao : Order By CASE not working

Question

我有一个使用 Dao 处理查询等的 Room 数据库。我正在使用静态(非实时数据)函数通过查询检索结果，当我手动硬编码 Order By 值和列时，一切正常，如下所示，但是在传递参数时到 Dao 进行排序，Order By 恢复为默认值(order by id 列)并且不根据传递的排序参数检索结果

硬编码的 Dao 示例
作品，结果按 ASC 或 DESC 排序

@Query("SELECT * FROM cameras WHERE suburb LIKE '%' || :suburb || '%' AND postcode LIKE '%' || :postcode || '%' ORDER BY direction ASC LIMIT :limit OFFSET :offset ")
fun getCamerasViaStatic(suburb: String?, postcode: String?, limit: Int?, offset: Int?): List<CamerasModel>

//and results retrieved in fragment using

CamerasApplicationDatabase.getInstance(context!!).CamerasDao().getCamerasViaStatic("", "", limit, offset)

Sort Param 传递给 Dao 示例
不行，结果默认排序 sort

@Query("SELECT * FROM cameras WHERE suburb LIKE '%' || :suburb || '%' AND postcode LIKE '%' || :postcode || '%' ORDER BY :sort_by ASC LIMIT :limit OFFSET :offset ")
fun getCamerasViaStatic(suburb: String?, postcode: String?, limit: Int?, offset: Int?, sort_by: String): List<CamerasModel>

//and results retrieved in fragment using

var sort_by = "my_column_to_sort_by"
CamerasApplicationDatabase.getInstance(context!!).CamerasDao().getCamerasViaStatic("", "", limit, offset, sort_by)

不知道为什么考虑到传递的其他参数在两个示例中仍然有效，为什么这个添加不起作用，问题也是，稍后我通过 ASC/DESC 参数并在 CASE WHEN 中使用(下面的示例)

ORDER BY CASE WHEN :sort = 1 THEN :sort_by END ASC, CASE WHEN :sort = 0 THEN :sort_by END DESC

Answer 1

使用多个 CASE 表达式找到解决方案...解决方案来自以下链接

Room user configurable order by queries

Room database full dynamic query

@Query("SELECT * FROM cameras " +
        "WHERE suburb LIKE '%' || :suburb || '%' AND postcode LIKE '%' || :postcode || '%' " +
        "ORDER BY " +
        "CASE WHEN :sort_by = 'description'  AND :sort = 0 THEN description END DESC, " +
        "CASE WHEN :sort_by = 'description'  AND :sort = 1 THEN description END ASC, " +
        "CASE WHEN :sort_by = 'direction'    AND :sort = 0 THEN direction   END DESC, " +
        "CASE WHEN :sort_by = 'direction'    AND :sort = 1 THEN direction   END ASC, " +
        "CASE WHEN :sort_by = 'location'     AND :sort = 0 THEN locality    END DESC, " +
        "CASE WHEN :sort_by = 'location'     AND :sort = 1 THEN locality    END ASC, " +
        "CASE WHEN :sort_by = 'state'        AND :sort = 0 THEN state       END DESC, " +
        "CASE WHEN :sort_by = 'state'        AND :sort = 1 THEN state       END ASC " +
        "LIMIT :limit " +
        "OFFSET :offset "
)

fun getCamerasUsingPaginationStatic(suburb: String?, postcode: String?, limit: Int?, offset: Int?, sort: Int?, sort_by: String?): List<CamerasModel>

如果您需要基于 id/values/etc 的数组过滤结果，下面也是相同的查询类型，但传递了 ids 数组(使用 IN(:filteredBookmarkedItems))...

@Query("SELECT * FROM cameras " +
        "WHERE camera_id IN(:filteredBookmarkedItems) AND suburb LIKE '%' || :suburb || '%' AND postcode LIKE '%' || :postcode || '%' " +
        "ORDER BY " +
        "CASE WHEN :sort_by = 'description'  AND :sort = 0 THEN description END DESC, " +
        "CASE WHEN :sort_by = 'description'  AND :sort = 1 THEN description END ASC, " +
        "CASE WHEN :sort_by = 'direction'    AND :sort = 0 THEN direction   END DESC, " +
        "CASE WHEN :sort_by = 'direction'    AND :sort = 1 THEN direction   END ASC, " +
        "CASE WHEN :sort_by = 'location'     AND :sort = 0 THEN locality    END DESC, " +
        "CASE WHEN :sort_by = 'location'     AND :sort = 1 THEN locality    END ASC, " +
        "CASE WHEN :sort_by = 'state'        AND :sort = 0 THEN state       END DESC, " +
        "CASE WHEN :sort_by = 'state'        AND :sort = 1 THEN state       END ASC " +
        "LIMIT :limit " +
        "OFFSET :offset "
)
fun getBookmarkedCamerasUsingPaginationStatic(filteredBookmarkedItems: List<Int>, suburb: String?, postcode: String?, limit: Int?, offset: Int?, sort: Int?, sort_by: String?): List<CamerasModel>