英文:
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"
)
英文:
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"
)
答案1
得分: 1
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 ENDconstruct 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 SELECT and ORDER accordingly. The final output being the 3 queries combined using UNION.
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):-
[![enter image description here][2]][2]
[![enter image description here][3]][3]
- 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 value */
" /* 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
<details>
<summary>英文:</summary>
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 `SELECT`s and `ORDER`s accordingly. The final output being the 3 queries combined using `UNION`s.
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):-
[![enter image description here][1]][1]
[![enter image description here][2]][2]
[![enter image description here][3]][3]
- 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
or
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.
[1]: https://i.stack.imgur.com/lHljR.png
[2]: https://i.stack.imgur.com/BexII.png
[3]: https://i.stack.imgur.com/zOAwx.png
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论