问题描述
我有一个使用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 examplenot 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:按案例排序不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!