Android Room database view

Question

in android docs code for creating view in room database is

@DatabaseView("SELECT user.id, user.name, user.departmentId," +
    "department.name AS departmentName FROM user " +
    "INNER JOIN department ON user.departmentId = department.id")
data class UserDetail(
var id: Long,
var name: String?,
var departmentId: Long,
var departmentName: String?
)

but how i can create view with some dynamic condition like user.id=userId where userId=1 or 2 or some integer

Answer 1

You can have Where clause in the query when creating a database view, but this will not be dynamic. Let's say you want to have a database view that only returns active users that are assigned to any department, so you could have Where user.active = true or something like that. But this will be set on the schema, so everytime you query this view, it will only return active users.

When adding a new user to the User table, the view will be automatically updated.

But you don't need to have this Where clause when creating the database view. Once the view is created, you can use as a normal table, so you can have a DAO that queries only the users that have the desired id.

For example:

@Query("SELECT * FROM UserDetail WHERE id = :id")
fun search(id: Int): List<UserDetail>

So your view is a "table" that joins user and department and then you can query as a normal table and have filters.

Does this help?