Android Room Dao: Order By Case Not Working
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 Or
Solution 1:
Found solution using multiple CASE Expressions ... solution helped from below links
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 "
)fungetCamerasUsingPaginationStatic(suburb: String?, postcode: String?, limit: Int?, offset: Int?, sort: Int?, sort_by: String?): List<CamerasModel>
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 "
)fungetBookmarkedCamerasUsingPaginationStatic(filteredBookmarkedItems: List<Int>, suburb: String?, postcode: String?, limit: Int?, offset: Int?, sort: Int?, sort_by: String?): List<CamerasModel>
Post a Comment for "Android Room Dao: Order By Case Not Working"