android - 房间查询 : find within list is always returning null

Question

我有一个实体，其中一个字段是 MutableList。我想返回该列表中包含给定 ID 的所有用户 ID。查询总是返回一个空列表。如果我打开数据库，虽然我可以看到字段存储正确并且有用户 ID 要返回。我究竟做错了什么？

数据类:

@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
interface UserDao {
   @Query("SELECT * FROM user WHERE postsIds LIKE :id")
   fun getForPost(id: String): List<User>

// some other queries
}

存储库:

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
    }

用法:

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)
})

Answer 1

据我所知，您似乎在使用 LIKE(通配符搜索) 而不是 =(精确匹配)运算符:

    @Dao
    interface UserDao {

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

        // some other queries
    }

现在我确定您有自己的用例来执行此操作。使用 Room 1.1.1+ 时，您需要将通配符 % 添加到您的 LIKE 运算符中，如下所示，否则可能不起作用:

    @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
}

只是另一个提示，如果您真的想在 Room 中使用全文搜索，那么我建议您更新到添加了 FTS4 的 v2.1支持，这里有一个很好的详细阅读 Medium