Search code examples
javaandroidandroid-room

ROOM ORDER BY CASE: Where Can I define the optional parameter?

I would like insert the Query in DAO interface like below.

@Query("SELECT * FROM words WHERE childId = :childId ORDER BY " +
"CASE WHEN parameter = 0 THEN dateTime END DESC, " +
"CASE WHEN parameter = 1 THEN dateTime END ASC, " +
"CASE WHEN parameter = 2 THEN wordName END DESC, " +
"CASE WHEN parameter = 3 THEN wordName END ASC")
List<Word> findWord(int childId, int parameter);

But I can't figure out where and how define the optional parameter used after CASE WHEN(below).

private int parameter;

How can I get it? Thank you.

Solution

  • I believe you could use-

    @Query("SELECT * FROM words WHERE childId = :childId ORDER BY " +
"CASE WHEN :parameter = 0 THEN dateTime END DESC, " +
"CASE WHEN :parameter = 1 THEN dateTime END ASC, " +
"CASE WHEN :parameter = 2 THEN wordName END DESC, " +
"CASE WHEN :parameter = 3 THEN wordName END ASC")
List<Word> findWord(int childId, int parameter);

    i.e. : to indicate where the parameter value is to be used (bound). Room knows to bind the same value multiple times

    Additional re the comment:-

    I partially recant my comment, the query might run, but it is not what the OP intends. The OP's intended logic is to order by just one column in ascending or descending order. Your answer (and the OP's original query) are ordering by four columns.

    Using slightly adapted Query (to not take into account the childId column (to simplify/reduce insertions to suit)) as per:-

    @Query(
    "SELECT * FROM words /*WHERE childId = :childId*/ ORDER BY " +
            "CASE WHEN :parameter = 0 THEN dateTime END DESC, " +
            "CASE WHEN :parameter = 1 THEN dateTime END ASC, " +
            "CASE WHEN :parameter = 2 THEN wordName END DESC, " +
            "CASE WHEN :parameter = 3 THEN wordName END ASC"
)
fun findWord(/*childId: Int,*/ parameter: Int): List<Words>

    and then using the following code in an activity (allowMainThreadQueries for brevity):-

        daoDB1.insert(Words(wordName = "C", datetime = (System.currentTimeMillis() / 1000) - (10 * onedayAsLong)))
    daoDB1.insert(Words(wordName = "Z", datetime = (System.currentTimeMillis() / 1000) + (8 * onedayAsLong)))
    daoDB1.insert(Words(wordName = "A", datetime = (System.currentTimeMillis() / 1000) - (7 * onedayAsLong)))
    daoDB1.insert(Words(wordName = "B", datetime = (System.currentTimeMillis() / 1000) - (8 * onedayAsLong)))
    daoDB1.insert(Words(wordName = "Y", datetime = (System.currentTimeMillis() / 1000) + (10 * onedayAsLong)))


    for (w in daoDB1.findWord(0)) {
        Log.d("DBINFO_RSLT1","Word is ${w.wordName} DateTime is ${w.datetime} ID is ${w.childId}")
    }
    for (w in daoDB1.findWord(1)) {
        Log.d("DBINFO_RSLT2","Word is ${w.wordName} DateTime is ${w.datetime} ID is ${w.childId}")
    }
    for (w in daoDB1.findWord(2)) {
        Log.d("DBINFO_RSLT3","Word is ${w.wordName} DateTime is ${w.datetime} ID is ${w.childId}")
    }
    for (w in daoDB1.findWord(3)) {
        Log.d("DBINFO_RSLT4","Word is ${w.wordName} DateTime is ${w.datetime} ID is ${w.childId}")
    }
    for (w in daoDB1.findWord(99 /*>>>>>>>>>> OOOPS??? <<<<<<<<<<*/)) {
        Log.d("DBINFO_RSLT5","Word is ${w.wordName} DateTime is ${w.datetime} ID is ${w.childId}")
    }

    Then the result in the log is:-

    2023-03-04 15:42:56.237 D/DBINFO_RSLT1: Word is Y DateTime is 1686544976 ID is 5
2023-03-04 15:42:56.238 D/DBINFO_RSLT1: Word is Z DateTime is 1684816976 ID is 2
2023-03-04 15:42:56.238 D/DBINFO_RSLT1: Word is A DateTime is 1671856976 ID is 3
2023-03-04 15:42:56.238 D/DBINFO_RSLT1: Word is B DateTime is 1670992976 ID is 4
2023-03-04 15:42:56.238 D/DBINFO_RSLT1: Word is C DateTime is 1669264976 ID is 1


2023-03-04 15:42:56.243 D/DBINFO_RSLT2: Word is C DateTime is 1669264976 ID is 1
2023-03-04 15:42:56.243 D/DBINFO_RSLT2: Word is B DateTime is 1670992976 ID is 4
2023-03-04 15:42:56.243 D/DBINFO_RSLT2: Word is A DateTime is 1671856976 ID is 3
2023-03-04 15:42:56.243 D/DBINFO_RSLT2: Word is Z DateTime is 1684816976 ID is 2
2023-03-04 15:42:56.243 D/DBINFO_RSLT2: Word is Y DateTime is 1686544976 ID is 5


2023-03-04 15:42:56.245 D/DBINFO_RSLT3: Word is Z DateTime is 1684816976 ID is 2
2023-03-04 15:42:56.246 D/DBINFO_RSLT3: Word is Y DateTime is 1686544976 ID is 5
2023-03-04 15:42:56.246 D/DBINFO_RSLT3: Word is C DateTime is 1669264976 ID is 1
2023-03-04 15:42:56.246 D/DBINFO_RSLT3: Word is B DateTime is 1670992976 ID is 4
2023-03-04 15:42:56.246 D/DBINFO_RSLT3: Word is A DateTime is 1671856976 ID is 3


2023-03-04 15:42:56.249 D/DBINFO_RSLT4: Word is A DateTime is 1671856976 ID is 3
2023-03-04 15:42:56.249 D/DBINFO_RSLT4: Word is B DateTime is 1670992976 ID is 4
2023-03-04 15:42:56.249 D/DBINFO_RSLT4: Word is C DateTime is 1669264976 ID is 1
2023-03-04 15:42:56.249 D/DBINFO_RSLT4: Word is Y DateTime is 1686544976 ID is 5
2023-03-04 15:42:56.250 D/DBINFO_RSLT4: Word is Z DateTime is 1684816976 ID is 2


2023-03-04 15:42:56.251 D/DBINFO_RSLT5: Word is C DateTime is 1669264976 ID is 1
2023-03-04 15:42:56.251 D/DBINFO_RSLT5: Word is Z DateTime is 1684816976 ID is 2
2023-03-04 15:42:56.251 D/DBINFO_RSLT5: Word is A DateTime is 1671856976 ID is 3
2023-03-04 15:42:56.251 D/DBINFO_RSLT5: Word is B DateTime is 1670992976 ID is 4
2023-03-04 15:42:56.251 D/DBINFO_RSLT5: Word is Y DateTime is 1686544976 ID is 5
    • i.e. all appear to be sorted as probably expected (last set RSLT5 purposefully not meeting any of the CASE conditions).