Android Room: Order By not working

user_3127851 0
Viewed 27305

I am using the new Android ORM Room. And I faced the following issue, queries that use ORDER BY with arguments don't work.

If I want to use the field populated from a parameter for ORDER BY it does not work. It just doesn't sort anything.

@Query("SELECT * FROM User ORDER BY :orderBY ASC")
List<User> sortedFind(String orderBY);

But, when I put the ORDER BY column directly in the query to sort the results, then it works as expected.

@Query("SELECT * FROM User ORDER BY name ASC")
List<User> sortedFind();

Is it a bug on Android Room, or am I doing something wrong?

android-architecture-components android-room android
5 Answers

If you are trying order by on string field then you may face string upper and lower case issue, Then you should try with LOWER(your_string_field_name) or UPPER(your_string_field_name) Like below:

@Query("SELECT * FROM User ORDER BY LOWER(name) ASC"

Enjoy coding :)

user_60752310

You should use @RawQuery annotation and SupportSQLiteQuery class for runtime query

In Dao:

 @RawQuery
 List<Objects> runtimeQuery(SupportSQLiteQuery sortQuery);

To get data:

String query ="SELECT * FROM User ORDER BY " + targetField + " ASC";
List<Objects> users = appDatabase.daoUser().runtimeQuery(new SimpleSQLiteQuery(query));
user_48587570

you can only arrange data in ascending order in reference to name of any of column. Programatically you cannot pass any value to Dao methods

user_69297400

The Problem

That's not the right way to pass the ORDER BY-column, Because the Room Database does not recognize it.

Any passing String data to the @Query Statment, RoomDB going to add Single Quotation Marks to it, So for example in your code:

@Query("SELECT * FROM User ORDER BY :orderBY ASC")
List<User> sortedFind(String orderBY);

when you call the sortedFind function:

sortedFind("UserId");

The query for RoomDB going to be like SELECT * FROM User ORDER BY 'UserId' ASC, So the problem here 'UserId' is not a column.

The Right Way to do this

By using the SQLite CASE To check the Column Name and set the ORDER BY-column By that Column, like the Following:

@Query("SELECT * FROM User "+
        " ORDER BY  "+
        "      CASE :orderBY WHEN 'UserId' THEN UserId END DESC," +
        "      CASE :orderBY WHEN 'UserName' THEN UserName END DESC," +
        "      CASE :orderBY WHEN 'UserAge' THEN UserAge END DESC" )
List<User> sortedFind(String orderBY);
user_184601150
Related