19

I am migrating an app to use Room from normal Sqlite and one part that I am having trouble with is that there are several queries that have an order by statement that are user configurable, meaning they can change how they want to view the list order.

What it seems is the Room does not allow for dynamic order by statements so I would have to make individual queries specific for each order by statement.

Has anyone found a better way around this issue so I can have 1 query statement where the only thing that changes is the order by clause vs having to write what in my case would be about 15 extra query statements all basically the same?

tyczj
  • 71,600
  • 54
  • 194
  • 296
  • Possible duplicate of [How to dynamically query the room database at runtime?](https://stackoverflow.com/questions/44287465/how-to-dynamically-query-the-room-database-at-runtime) – Bertram Gilfoyle Aug 18 '18 at 17:57

4 Answers4

15

I'm facing the same problem right now. The thing that can be done is to use CASE:

SELECT * FROM Table
ORDER BY 
CASE WHEN :parameter = 1 THEN Column END ASC,
CASE WHEN :parameter = 2 THEN Column2 END DESC

But I'm assuming you are using a ViewModel as well, so you will probably have to reinitialise it every time. I think this way is a little bit better than writing 15 queries, maybe not as compact though.

This answer also has a nice example, but the logic is reversed I believe.

Suleyman
  • 2,765
  • 2
  • 18
  • 31
  • 1
    thanks, this helped solve my question here https://stackoverflow.com/questions/61055772/android-room-dao-order-by-case-not-working/61070980#61070980 – BENN1TH Apr 07 '20 at 08:22
  • Not usable, if one of case conditions for order by clause contain multiple fields (order by firstName ASC, lastName DESC for example). ie "order by case when X then firstName ASC, lastName DESC else dateTimeCreate end" – A. Petrov Sep 29 '20 at 14:23
7

In Room 1.1 there is now a RawQuery that can be used that solves this issue

 @Dao
 interface RawDao {
     @RawQuery
     User getUserViaQuery(SupportSQLiteQuery query);
 }
 SimpleSQLiteQuery query = new SimpleSQLiteQuery("SELECT * FROM User WHERE id = ? LIMIT 1",
         new Object[]{userId});
 User user2 = rawDao.getUserViaQuery(query);

https://developer.android.com/reference/android/arch/persistence/room/RawQuery

tyczj
  • 71,600
  • 54
  • 194
  • 296
6

As tyczj already suggested you can us a RawQuery. In addition, the SimpleSQLiteQuery can also be moved into the DAO so there are no SQL statements outside of the DAO.

A DAO can also be defined via an abstract class. This allows you to add a method which executes the SimpleSQLiteQuery.

Example:

@DAO
public abstract class UserDao {

    @RawQuery
    public abstract List<User> getUsersViaRawQuery(SupportSQLiteQuery query);

    public List<User> getUsersOrderBy(String column) {
        String statement = "SELECT * FROM user ORDER BY " + column + " ASC";
        SupportSQLiteQuery query = new SimpleSQLiteQuery(statement, new Object[]{});
        return getUsersViaRawQuery(query);
    }

}

The column parameter could also be replaced by an enum, which only allows specific values.

Example:

private static String createOrderByString(Order order) {
    switch (order) {
        case NAME:
            return "ORDER BY first_name ASC, last_name ASC";
        case AGE:
            return "ORDER BY age DESC";
        default:
            return "";
    }
}
Matt Ke
  • 3,599
  • 12
  • 30
  • 49
-2

You can't use parameters in Our Dao's method. 

@Query("SELECT * FROM sometable order by :orderClause DESC")
    List<Islem> findOneUserName(String orderClause);//didn't work

This Query parameters only use in where parameters for avoid Sql injections.

anilkay
  • 144
  • 1
  • 8