Does room-compiler support FILTER clause?-CodePudding

I define Room Dao with Query like this:

@Query(
   "SELECT "   
   "COUNT(*) FILTER(WHERE f > 1) as count1 "  
   "FROM myTable WHERE ..."
)

and get an error: extraneous input '(' expecting {<EOF>, ';', K_ALTER, K_ANALYZE, K_ATTACH, K_BEGIN, K_COMMIT, K_CREATE, K_DELETE, K_DETACH, K_DROP, K_END, K_EXPLAIN, K_INSERT, K_PRAGMA, K_REINDEX, K_RELEASE, K_REPLACE, K_ROLLBACK, K_SAVEPOINT, K_SELECT, K_UPDATE, K_VACUUM, K_VALUES, K_WITH, UNEXPECTED_CHAR}

I suppose the compiler doesn't understand '(' after FILTER keyword. I use kapt "androidx.room:room-compiler:2.4.1" in my build.gradle. I have shown the simplified query, in the real one, I have more than one COUNT(*) fields and cannot move WHERE after FROM.

I used the aggregator syntax from https://www.sqlite.org/lang_aggfunc.html#count

CodePudding user response：

The FILTER clause for aggregate functions is supported by SQLite since version 3.30.0 (2019-10-04).
This version of SQLite is used by Android only for API Level 31 (which is currently the latest).
This means that if your code runs in a device with a lower API Level you will not be able to use this new feature.

An alternative would be conditional aggregation:

@Query(
   "SELECT "   
   "COUNT(CASE WHEN f > 1 THEN 1 END) AS count1 "  
   "FROM myTable WHERE ..."
)

or with TOTAL() aggregate function:

@Query(
   "SELECT "   
   "TOTAL(f > 1) AS count1 "  
   "FROM myTable WHERE ..."
)