找到使用多个 CASE 表达式的解决方案...解决方案从以下链接中得到帮助
房间用户可通过查询配置订单 https://stackoverflow.com/questions/50104554/room-user-configurable-order-by-queries#
房间数据库全动态查询 https://stackoverflow.com/questions/48172807/room-database-full-dynamic-query/48173542#48173542
@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/值/等数组过滤结果,则传递 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>