Android Room Dao:按案例排序不起作用 [英] Android Room Dao: Order By CASE not working

问题描述

我有一个使用Dao处理查询等的Room数据库.我使用静态(非实时数据)功能通过查询检索结果，当我手动对如下的Order By值和列进行硬编码时，所有方法都工作正常将参数传递给Dao进行排序时，Order By会恢复为默认值(按ID排序的列)，并且不会基于传递的排序参数来检索结果

I have a Room database using Dao to process queries etc. I am using static (non live data) function to retrieve results via the query, all works well when I manual hard code the Order By values and column as below, however when passing params to the Dao to do the sorting, the Order By reverts back to default (order by id column) and does not retrieve results based on the passed sort param

硬编码的道例 有效，结果按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 Param passed to Dao example not work, results sorted by default 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中使用(以下示例)

Not sure why this addition does not work considering the other params passed still work in both examples, the issue is also, later on I pass the ASC/DESC param and use in CASE WHEN (example below)

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

推荐答案

找到了使用多个CASE表达式的解决方案...解决方案从下面的链接中获得了帮助

Found solution using multiple CASE Expressions ... solution helped from below links

按查询设置用户可配置的房间

房间数据库完全动态查询

@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数组过滤结果，则传递ID数组(使用IN(:filteredBookmarkedItems)).

also below is same query type but with array of ids passed (using IN(:filteredBookmarkedItems)) if you need to filter results based on a array of id/values/etc...

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

这篇关于Android Room Dao:按案例排序不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！