Room Query: find within list is always returning null

user_4809922 0
Viewed 1259

I have an entity where one of the fields is a MutableList. I want to return all the user ids that contain the given ID in that list. The query always return an empty list. If I open the database though I can see that the fields are stored properly and that there are user IDs to be returned. What am I doing wrong?

Data class:

@Entity
data class User(
  @PrimaryKey
  @SerializedName("id")
  @ColumnInfo(name = "userId")
  var userId: String,
  @SerializedName("username")
  var userName: String = "",
  var city: String = "",
  var postsIds: MutableList<String>
)

Dao: 

@Dao
interface UserDao {
   @Query("SELECT * FROM user WHERE postsIds LIKE :id")
   fun getForPost(id: String): List<User>

// some other queries
}

Repository: 

fun getUsersForPost(id: String): LiveData<List<User>> {
        val data = MutableLiveData<List<User>>()
        GlobalScope.launch {
            val query = async(Dispatchers.IO) { userDao.getForPost(id) }
            val result = query.await()
            if (result.isNullOrEmpty()) {
                // todo fetch from the API
            } else {
                data.postValue(result)
            }
        }
        return data
    }

Usage: 

ViewModel: 

fun getPost(id: String): Post {
  val post = repository.getPost(id)
  _postEditors.value = repository.getUsersForPost(id).value
  return post
}

Fragment: 

viewModel.postEditors.observe(this, Observer { 
  Log.d(TAG, $it)
})
android-architecture-components android-room kotlin android
2 Answers

From what I can tell you seem to be using the LIKE(wildcard search) instead of =(exact match) operator:

    @Dao
    interface UserDao {

        @Query("SELECT * FROM user WHERE postsIds = :id")
        fun getForPost(id: String): List<User>

        // some other queries
    }

Now I'm sure you have your own use cases for doing that. When using Room 1.1.1+ you need to add a wildcard % to your LIKE operator as follows otherwise it might not work:

    @Dao
    interface UserDao {

        @Query("SELECT * FROM user WHERE postsIds LIKE `%` || :id || `%`")
        fun getForPost(id: String): List<User>

        // some other queries
    }

N.B: || is concatenation operator and % wildcard read up on SQLite wildcards

This would result in a search for anything that matches the provided id a.k.a: Full Text Search, if you only want to match anything starting with id then you'd do the following:

   @Dao
   interface UserDao {

       @Query("SELECT * FROM user WHERE postsIds LIKE :id || `%`")
       fun getForPost(id: String): List<User>

       // some other queries
}

Just another hint, if you really want to use Full Text Search with Room then i'd suggest you update to v2.1 which added FTS4 support, and here's a nice detailed read on Medium

user_17253470

Just by reviewing your code, I think you're doing userDao.getForPost(id) but likely you should be doing userDao.getForPost("%"+id+"%")

user_31006010
Related