Issue
In Android room relation, is it possible to use search query using the property of the related table. Below is my table structure. In this i am relating transaction with payment and lines(transaction items). I have an search field in my UI where the user could search using payment amount which is inside payment table. How to form a query to access the properties of payment table.
class TransactionWithPaymentAndLines(
@Embedded
var transactions: Transactions? = null,
@Relation(
parentColumn = "id",
entityColumn = "transactionId",
entity = Payment::class
)
var payments: List<Payment> = listOf(),
@Relation(
parentColumn = "id",
entityColumn = "transactionId",
entity = TransactionLines::class
)
var transactionLines: List<TransactionLines> = listOf()
)
Solution
Ideal way is to query multiple related tables is to create a View. A view combines data from two or more tables using join.
In Android, using Room Persistance library, you can create such a view, and then you can query the fields of view. This is how you can do it:
Suppose, you have tables:
User: id, name, departmentId
Department: id, name
Create a View:
@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(
val id: Long,
val name: String?,
val departmentId: Long,
val departmentName: String?
)
Add View to Database:
@Database(entities = arrayOf(User::class),
views = arrayOf(UserDetail::class), version = 1)
abstract class AppDatabase : RoomDatabase() {
abstract fun userDetailDao(): UserDetailDao
}
Create a DAO:
@Dao
interface UserDetailDao {
@Query("SELECT * FROM UserDetail")
fun loadAllUserDetails(): Array<UserDetail>
}
Now, you can query a View using this DAO.
Answered By - Chintan Soni
Answer Checked By - Katrina (JavaFixing Volunteer)