Using Room query to filter out multiple items?

I'm unable to get a room query to allow for a multiple wildcard query against my db.

    @Query (
        """
        SELECT * FROM sports 
        WHERE accountId = :accountId
        AND title NOT LIKE (:filters)
        ORDER BY date DESC
        """
    )
    fun queryForSports(
        accountId: Int,
        filters: Set<String>,
    )

Trying to do this, will not work if I pass multiple items to it. For instance if I only pass "%golf%", it works just fine. Like this...

var filters = listOf("%golf%").toSet()
sportDao.queryForSports(accountId, filters)

But if I want to filter out multiple items, by updating the list, it filters everything, and no results come back.

var filters = listOf("%golf%", "%tennis%").toSet()
sportDao.queryForSports(accountId, filters)

Is there a way to use the wildcard (%) for "NOT LIKE", for multiple items? I can't seem to find a good way to do it. I also tried, "NOT IN ( )" rather than NOT LIKE, and it behaved the same way, incorrectly.

A lot of times in these situations you would include an "OR ", but the way that room is structured, I don't see any meaningful way to do this simply by passing a Set as a parameter to the queryForSports() function.

Solution

You could have a function with a body that builds the SQL for use by an @RawQuery, noting that the SQL cannot be checked at compile time, by looping through the collect/array of filters.

You could have a recursive/CTE based query WITH RECURSIVE .... that separates the filter (e.g. if it were passed as a CSV).

This probably the most complex.

You could have a function with a body that

loops through the filter collection/array that extracts the rowids (adding them to a Long array/collection) of the respective rows to be excluded (to be excluded), and then
1. this would utilise a previously created @Query annotated function that takes the single filter.
that then drives a final query that uses the .... WHERE rowid NOT IN(:suitable_var_or_val_for_the_collecttion_of_rowids) ....
1. it would not matter if the same rowid were extracted multiple times

note that rowid is a column (normally hidden) that, at least for Room, will always exist and will always identify a single row. If a column, is defined as being the PRIMARY KEY and if that column is an INTEGER type then that column will be an alias of the rowid.
noting that Room correctly provides a suitable delimited CSV for use by IN e.g. "'v1','v2'" etc.
I would suggest that this is the simplest and it would

Demo

For ease, preferring the last option from the above then consider the following:-

In the @Dao annotated interface (AllDAOs) the following:-

@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(sports: Sports): Long

@Query("SELECT rowid FROM sports WHERE accountid=:accountid AND title LIKE('%'||:filter||'%')")
fun getRowidsForAFilter(accountid: String,filter: String): LongArray
@Query("SELECT * FROM sports WHERE rowid NOT IN(:rowidList)")
fun getNOTFilteredSports(rowidList: LongArray): List<Sports>

@Transaction
@Query("")
fun getNOTFilteredSportDriver(accountid: String,filters: List<String>): List<Sports> {
    val rowidlist = ArrayList<Long>()
    for (f in filters) {
         for(currentRowid in  getRowidsForAFilter(accountid,f)) {
             rowidlist.add(currentRowid)
         }
    }
    return getNOTFilteredSports(rowidlist.toLongArray())
}

Noting that the @Entity annotated class Sports is:-

@Entity
data class Sports(
    @PrimaryKey
    val pk: Long?=null,
    val accountid: String,
    val title: String,
    val date: Long
)

i.e. there is no rowid column (instead the pk column is an alias of the rowid column as per SQLite)

Now consider the following activity code (note the main thread has been used for brevity):-

class MainActivity : AppCompatActivity() {
    lateinit var db: CanaryDatabase
    lateinit var dao: AllDAOs
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = CanaryDatabase.getInstance(this)
        dao = db.getAllDAOs()

        dao.insert(Sports(accountid = "A001", title = "FRED BLOGGS", date = System.currentTimeMillis() / 1000))
        dao.insert(Sports(accountid = "A001", title = "TOM THUMB", date = System.currentTimeMillis() / 1000))
        dao.insert(Sports(accountid = "A001", title = "JANE DOE", date = System.currentTimeMillis() / 1000))
        dao.insert(Sports(accountid = "A001", title = "BOB SMITH", date = System.currentTimeMillis() / 1000))
        dao.insert(Sports(accountid = "A001", title = "JOHN DOE", date = System.currentTimeMillis() / 1000))

        val f1 = listOf<String>("OB","LO","UM")
        for (s in dao.getNOTFilteredSportDriver("A001",f1)) {
            Log.d("DBINFO","SPORT pk is ${s.pk} AccountID is ${s.accountid} Date is ${s.date} Title is ${s.title}")
        }

for simplicity/brevity the database has been grabbed from a previous answer, hence it's name.

When the above is run then the log contains:-

D/DBINFO: SPORT pk is 3 AccountID is A001 Date is 1731918959 Title is JANE DOE
D/DBINFO: SPORT pk is 5 AccountID is A001 Date is 1731918959 Title is JOHN DOE

i.e.

FRED BLOGGS has been excluded due to the filter LO in BLOGGS
TOM THUMB has been excluded due to UM
BOB SMITH has been excluded due to the OB