Room DAO Order By ASC or DESC variable

Question

I'm trying to make a @Query function in my @Dao interface which has a boolean parameter, isAsc to determine the order:

@Query("SELECT * FROM Persons ORDER BY first_name (:isAsc ? ASC : DESC)")
List<Person> getPersonsAlphabetically(boolean isAsc);

Apparently this isn't allowed. Is there a work around here?

EDIT:

It seemed odd to use two queries (below) since the only difference is ASC and DESC:

@Query("SELECT * FROM Persons ORDER BY last_name ASC")
List<Person> getPersonsSortByAscLastName();

@Query("SELECT * FROM Persons ORDER BY last_name DESC")
List<Person> getPersonsSortByDescLastName();

score 63 · Accepted Answer · edited Sep 10 '20 at 05:46

63

Use CASE Expression for SQLite to achieve this in Room DAO,

@Query("SELECT * FROM Persons ORDER BY 
        CASE WHEN :isAsc = 1 THEN first_name END ASC, 
        CASE WHEN :isAsc = 0 THEN first_name END DESC")
List<Person> getPersonsAlphabetically(boolean isAsc);

edited Sep 10 '20 at 05:46

Amin Keshavarzian

3,646
1
37
38

answered Mar 22 '19 at 10:53

Chandan Sharma

2,803
1
17
25

FYI the sqlite query optimizer will not properly optimize this. It will always generate a temporary B-Tree. This is especially true with the older version of SQLite supplied with android. – HSchmale Feb 10 '21 at 22:02

score 13 · Answer 2 · edited Mar 22 '19 at 10:46

13

Create two queries, one with ASC and one with DESC.

@Query("SELECT * FROM Persons ORDER BY last_name ASC")
List<Person> getPersonsSortByAscLastName();

@Query("SELECT * FROM Persons ORDER BY last_name DESC")
List<Person> getPersonsSortByDescLastName();

edited Mar 22 '19 at 10:46

aLL

1,596
3
17
30

answered Mar 22 '19 at 10:05

just

1,900
4
25
46

9

this seems odd to me, creating two queries where the only difference are the ASC and DESC – aLL Mar 22 '19 at 10:27
1

yes, not the solution you were looking for apparently – Amin Keshavarzian Sep 09 '20 at 19:26

score -8 · Answer 3 · answered Mar 22 '19 at 10:32

-8

Why don't you try something like this? I have not tested it.

@Query("SELECT * FROM Persons ORDER BY first_name :order")
List<Person> getPersonsAlphabetically(String order);

And the logic you suggested should go before you make the query.

answered Mar 22 '19 at 10:32

Miguel Isla

1,379
14
25

Tried it. Doesn't work. Gives the error: (', '.', ASC, BETWEEN, COLLATE, DESC, IN, LIMIT, comma or semicolon expected, got ':order. Plus.. this is prone to error due to "ACS" and "DECS". you know – aLL Mar 22 '19 at 11:05
Are you sure you wrote it well? – Miguel Isla Mar 22 '19 at 11:12
Also this is no more error prone that any other code where you have to write by hand ASC or DESC like in your example.... – Miguel Isla Mar 22 '19 at 11:27
it was written well on [this](https://stackoverflow.com/q/51536310/8322877) and got the same error. IDE readily interprets sql queries and expects reserved sql words after ORDER BY not a variable string. A boolean variable would lessen if not fully eliminate error prone code – aLL Mar 22 '19 at 12:40
I guess then that what you have asked for can not be done. Sorry to hear that. A boolean may lessen or eliminate error prone code, I agree; but your example and my example works exactly the same because you have to convert from boolean to string. Thats what I mean in my previous comment. – Miguel Isla Mar 22 '19 at 12:49
5

I like these kind of answers because they're so wrong but teach us what NOT to do! Please do NOT delete this (attempt at an) answer. – Zun Apr 20 '20 at 12:19

Room DAO Order By ASC or DESC variable

3 Answers3

Linked