Search query using Android room relation

user_9620329 0
Viewed 1908

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()
)
android-room android
3 Answers

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.

user_17398820

Absolutely possible, you can use @Query in your DAO class please read Room Database Documentation

Examples of @Query

@Query("SELECT * FROM user")
List<User> getAll();

@Query("SELECT * FROM user WHERE uid IN (:userIds)")
List<User> loadAllByIds(int[] userIds);

@Query("SELECT * FROM user WHERE first_name LIKE :first AND " +
       "last_name LIKE :last LIMIT 1")
User findByName(String first, String last);
user_96887810

use @DB or @Query.

That should perfectly work...

@Query("SELECT * FROM TABLE_NAME")
List<Identifier> getAll();
user_67714800
Related