How can I add the sencond sort item when I use CASE to require parameter for SQLite with Room in Android Studio?

I have read the article

The Code A can work well in Android Studio.

At present, I hope to sort records by starred desc first, then by createdDate desc, so I replace Code A with Code B.

The Code B can be compiled and run, but I get the error result, all cases are pointed to "Case WHEN :sortBy = 'TEXT_ASC' THEN text END asc".

BTW, the Code C and Code D can't be compiled.

Code A

@Query("SELECT * FROM info_table ORDER BY " +
        "CASE WHEN :sortBy = 'START_PRIORITY' THEN starred END desc, " +         
        "Case WHEN :sortBy = 'TEXT_DESC' THEN text END desc, "+
        "Case WHEN :sortBy = 'TEXT_ASC' THEN text END asc"
)

Code B

@Query("SELECT * FROM info_table ORDER BY " +
        "CASE WHEN :sortBy = 'START_PRIORITY' THEN starred END desc, createdDate desc," +         
        "Case WHEN :sortBy = 'TEXT_DESC' THEN text END desc, "+
        "Case WHEN :sortBy = 'TEXT_ASC' THEN text END asc"
)

Code C

@Query("SELECT * FROM info_table ORDER BY " +
        "CASE WHEN :sortBy = 'START_PRIORITY' THEN starred ,createdDate END desc, " +         
        "Case WHEN :sortBy = 'TEXT_DESC' THEN text END desc, "+
        "Case WHEN :sortBy = 'TEXT_ASC' THEN text END asc"
)

Code D

@Query("SELECT * FROM info_table ORDER BY " +
         "CASE WHEN :sortBy = 'START_PRIORITY' THEN starred END desc, THEN createdDate END desc," +
         "Case WHEN :sortBy = 'TEXT_DESC' THEN text END desc, "+
         "Case WHEN :sortBy = 'TEXT_ASC' THEN text END asc"
)

Solution

I don't believe that what you want is feasible using CASE WHEN THEN ELSE END constructs as they can only be used where expressions are acceptable.

You may wish to refer to https://www.sqlite.org/lang_select.html
NOTE see Additional as using the CASE WHEN THEN ELSE END construct is feasible. However, using CTE's is probably easier to understand.

However, as it appears that you consider the other options, as per the link in your question, unacceptable then a 4th way could be to utilise common table expressions (CTE's) and or sub queries (the former often being simpler).

This method could involve 3 underlying queries (CTE's) only 1 of which will return any rows according to a WHERE clause that SELECTs and ORDERs accordingly. The final output being the 3 queries combined using UNIONs.

Consider this example/demo using an SQLite Tool (Navicat), noting that it demonstrates all 3 permutations:-

/* Cleanup Testing Environment (just in case) */
DROP TABLE IF EXISTS info_table;
/* Create the table and then load some testing data */
CREATE TABLE IF NOT EXISTS info_table (id INTEGER PRIMARY KEY, `text` TEXT, createdDate TEXT, starred TEXT);
INSERT INTO info_table (createdDate, starred, `text`) VALUES
    ('2023-01-01','starred001','ZZZZ')
    ,('2023-01-01','starred006','AAAA')
    ,('2022-01-01','starred009','YYYY')
    ,('2022-02-01','starred008','XXXX')
    ,('2022-03-01','starred007','WWWW')
;
/* First run */
WITH 
    /* optional but allows testing outside of room  in sqlite tool*/
    cte1(sortBy) AS (SELECT 'START_PRIORITY' /*'TEXT_DESC'*/ /*'TEXT_ASC'*/),
     /* only 1 of the following will select any rows due to the WHERE clause*/
    cte2 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'START_PRIORITY' ORDER BY createddate DESC, starred DESC),
    cte3 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'TEXT_DESC' ORDER BY `TEXT` DESC),
    cte4 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'TEXT_ASC' ORDER BY `TEXT` ASC)
 /* combine all three outputs 2 of which will return no rows */
SELECT * FROM cte2 UNION ALL SELECT * FROM cte3 UNION ALL SELECT * FROM cte4
;
/* Second run */
WITH 
    /* optional but allows testing outside of room  in sqlite tool*/
    cte1(sortBy) AS (SELECT /*'START_PRIORITY'*/ 'TEXT_DESC' /*'TEXT_ASC'*/),
     /* only 1 of the following will select any rows due to the WHERE clause*/
    cte2 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'START_PRIORITY' ORDER BY createddate DESC, starred DESC),
    cte3 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'TEXT_DESC' ORDER BY `TEXT` DESC),
    cte4 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'TEXT_ASC' ORDER BY `TEXT` ASC)
 /* combine all three outputs 2 of which will return no rows */
SELECT * FROM cte2 UNION ALL SELECT * FROM cte3 UNION ALL SELECT * FROM cte4
;
/* Thrid and final run */
WITH 
    /* optional but allows testing outside of room  in sqlite tool*/
    cte1(sortBy) AS (SELECT /*'START_PRIORITY'*/ /*'TEXT_DESC'*/ 'TEXT_ASC'),
     /* only 1 of the following will select any rows due to the WHERE clause*/
    cte2 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'START_PRIORITY' ORDER BY createddate DESC, starred DESC),
    cte3 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'TEXT_DESC' ORDER BY `TEXT` DESC),
    cte4 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'TEXT_ASC' ORDER BY `TEXT` ASC)
 /* combine all three outputs 2 of which will return no rows */
SELECT * FROM cte2 UNION ALL SELECT * FROM cte3 UNION ALL SELECT * FROM cte4
;
/* Cleanup Testing Environment */
DROP TABLE IF EXISTS info_table;

The results being (as per the run order):-

these results being appropriate.

It is then a simple matter of dropping one of the runs into an @Query's SQL and tailoring it to have the variable passed:-

e.g. :-

@Query("WITH " +
        "/* optional but allows testing outside of room  in sqlite tool*/" +
        "cte1(sortBy) AS (SELECT :sortBy)," + /* <<<<<<<<<< passed valued */
        " /* only 1 of the following will select any rows due to the WHERE clause*/" +
        "cte2 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'START_PRIORITY' ORDER BY createddate DESC, starred DESC)," +
        "cte3 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'TEXT_DESC' ORDER BY `TEXT` DESC)," +
        "cte4 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'TEXT_ASC' ORDER BY `TEXT` ASC)" +
        " /* combine all three outputs 2 of which will return no rows */" +
        "SELECT * FROM cte2 UNION ALL SELECT * FROM cte3 UNION ALL SELECT * FROM cte4" +
        ";")
fun getSortedViaCTEs(sortBy: String): List<InfoTable>

To actually demonstrate this, in comparison to B,C and D then consider the following activity code:-

db = TheDatabase.getInstance(this) dao = db.getAllDAOs()

    dao.deleteAllInfoTableRows()
    dao.insert(InfoTable(createdDate = "2023-01-01", starred = "starred001", text = "ZZZZ"))
    dao.insert(InfoTable(createdDate = "2022-01-01", starred = "starred009", text = "YYYY"))
    dao.insert(InfoTable(createdDate = "2022-02-01", starred = "starred008", text = "XXXX"))
    dao.insert(InfoTable(createdDate = "2022-03-01", starred = "starred007", text = "WWWW"))

    logInfo("SP",dao.getSorted("START_PRIORITY"))
    logInfo("TD",dao.getSorted("TEXT_DESC"))
    logInfo("TA",dao.getSorted("TEXT_ASC"))


    logInfo("V2SP",dao.getSortedViaCTEs("START_PRIORITY"))
    logInfo("V2TD",dao.getSortedViaCTEs("TEXT_DESC"))
    logInfo("V2TA",dao.getSortedViaCTEs("TEXT_ASC"))

with logInfo being:-

fun logInfo(tagSuffix: String, infoTableExtract: List<InfoTable>) {
    for (i in infoTableExtract) {
        Log.d("DBINFO_$tagSuffix","ID = ${i.id} CreatedDate = ${i.createdDate} Starred = ${i.starred} Text = ${i.text}")
    }
}

The results

From your code:-

2023-05-19 11:24:50.626 D/DBINFO_SP: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY
2023-05-19 11:24:50.626 D/DBINFO_SP: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 11:24:50.626 D/DBINFO_SP: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW
2023-05-19 11:24:50.626 D/DBINFO_SP: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ

2023-05-19 11:24:50.628 D/DBINFO_TD: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ
2023-05-19 11:24:50.628 D/DBINFO_TD: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW
2023-05-19 11:24:50.628 D/DBINFO_TD: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 11:24:50.628 D/DBINFO_TD: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY

2023-05-19 11:24:50.629 D/DBINFO_TA: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ
2023-05-19 11:24:50.629 D/DBINFO_TA: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW
2023-05-19 11:24:50.629 D/DBINFO_TA: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 11:24:50.629 D/DBINFO_TA: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY

obviously not the required results e.g. TD and TA are in the same order

From the CTE driven query:-

2023-05-19 11:24:50.632 D/DBINFO_V2SP: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ
2023-05-19 11:24:50.632 D/DBINFO_V2SP: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW
2023-05-19 11:24:50.632 D/DBINFO_V2SP: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 11:24:50.632 D/DBINFO_V2SP: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY

2023-05-19 11:24:50.636 D/DBINFO_V2TD: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ
2023-05-19 11:24:50.637 D/DBINFO_V2TD: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY
2023-05-19 11:24:50.637 D/DBINFO_V2TD: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 11:24:50.637 D/DBINFO_V2TD: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW

2023-05-19 11:24:50.639 D/DBINFO_V2TA: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW
2023-05-19 11:24:50.640 D/DBINFO_V2TA: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 11:24:50.640 D/DBINFO_V2TA: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY
2023-05-19 11:24:50.640 D/DBINFO_V2TA: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ

Additional

After thinking about the issue, the order of the CASE WHEN THEN ELSE END constructs matters and reordering them can achieve the desired results.

What needs to be considered is that whatever follows the END is included. Thus when the first construct is not matched then you will have something like:-

ORDER BY DESC, createdDate DESC,  TEXT DESC, ASC

ORDER BY DESC, createdDate DESC, DESC, TEXT ASC

and thus the highest priority sort will be the createdDate DESC and thus the TEXT column is the secondary sort and thus likely useless.

Noting that no resolved expression is basically the same value and hence the sort is not affected.

Now if you instead have:-

@Query("SELECT * FROM info_table ORDER BY " +
        "CASE WHEN :sortBy = 'TEXT_DESC' THEN text END desc,"+
        "CASE WHEN :sortBy = 'TEXT_ASC' THEN text END asc," +
        "CASE WHEN :sortBy = 'START_PRIORITY' THEN starred END desc, createdDate desc"
)
fun getSortedV2(sortBy: String): List<InfoTable>

Then the createdDate will always be the third sort order. So using:-

    logInfo("SP",dao.getSortedV2("START_PRIORITY"))
    logInfo("TD",dao.getSortedV2("TEXT_DESC"))
    logInfo("TA",dao.getSortedV2("TEXT_ASC"))

will result in:-

2023-05-19 12:00:38.386 D/DBINFO_SP: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY
2023-05-19 12:00:38.386 D/DBINFO_SP: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 12:00:38.386 D/DBINFO_SP: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW
2023-05-19 12:00:38.386 D/DBINFO_SP: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ

2023-05-19 12:00:38.389 D/DBINFO_TD: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ
2023-05-19 12:00:38.389 D/DBINFO_TD: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY
2023-05-19 12:00:38.390 D/DBINFO_TD: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 12:00:38.390 D/DBINFO_TD: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW

2023-05-19 12:00:38.393 D/DBINFO_TA: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW
2023-05-19 12:00:38.393 D/DBINFO_TA: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 12:00:38.393 D/DBINFO_TA: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY
2023-05-19 12:00:38.393 D/DBINFO_TA: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ

i.e. the orders appear to now be correct.