Is their a better way to populate ignored column from another table using room

Question

I have two related tables item and purchase. The purchase class contains an ignored column itemName which I want to fill with itemName from item

Although I have a method but I am concerned if there is a better way to achieve it because my way has to do with reading all items from the database then comparing purchase.itemOwnerID to item.itemID

Here is my populating code along with others

    LaunchedEffect(key1 = true) {
    sharedViewModel.requestAllPurchases()
    sharedViewModel.requestAllItems() //requesting all items 
}

val allPurchases by sharedViewModel.allPurchases.collectAsState()
val allItems by sharedViewModel.allItems.collectAsState() //getting all items requested



if (allPurchases is RequestState.Success && allItems is RequestState.Success) {
    (allPurchases as RequestState.Success<List<Purchase>>).data.forEach { purchase ->
        (allItems as RequestState.Success<List<Item>>).data.first { it.itemID == purchase.itemOwnerID }//comparism
            .apply {
                purchase.itemName = itemName
                purchase.costPrice = salePrice
            }
    }
}

Model

@Entity(tableName = "items", indices = [Index(value = ["itemName"], unique = true)])
    data class Item(
        @PrimaryKey(autoGenerate = true)
        var itemID: Int,
        var categoryOwnerID: Int = 0,
        var itemName: String,
        var costPrice: Int,
        var salePrice: Int,
    ) {
        @Ignore
        var desiredQuantity: Int = 0
    }

@Entity(tableName = "purchases")
data class Purchase(
    @PrimaryKey(autoGenerate = true)
    var purchaseID: Int,
    var itemOwnerID: Int,
    var quantity: Int,
    var soldPrice: Int,
) {
    @Ignore
    var itemName: String = ""
}

sealed class RequestState<out T> {
object Idle : RequestState<Nothing>()
object Loading : RequestState<Nothing>()
data class Success<T>(val data: T) : RequestState<T>()
data class Error(val error: Throwable) : RequestState<Nothing>()

}

@Dao

@Query("select * from items order by itemName asc")
fun getAllItems(): Flow<List<Item>>

@Query("select * from purchases order by purchaseID asc")
fun getAllPurchases(): Flow<List<Purchase>>

Repository

val getAllItems: Flow<List<Item>> = itemDao.getAllItems()
val getAllPurchases: Flow<List<Purchase>> = itemDao.getAllPurchases()

SharedViewModel

 private var _allItems = MutableStateFlow<RequestState<List<Item>>>(RequestState.Idle)
    val allItems: StateFlow<RequestState<List<Item>>> = _allItems

    fun requestAllItems() {
        _allItems.value = RequestState.Loading
        try {
            viewModelScope.launch {
                repository.getAllItems.collect {
                    _allItems.value = RequestState.Success(it)
                }
            }
        } catch (e: Exception) {
            _allItems.value = RequestState.Error(e)
        }

    }

private var _allPurchases =
        MutableStateFlow<RequestState<List<Purchase>>>(RequestState.Idle)
    val allPurchases: StateFlow<RequestState<List<Purchase>>> = _allPurchases

    fun requestAllPurchases() {
        _allPurchases.value = RequestState.Loading
        try {
            viewModelScope.launch {
                repository.getAllPurchases.collect {
                    _allPurchases.value = RequestState.Success(it)
                }
            }
        } catch (e: Exception) {
            _allPurchases.value = RequestState.Error(e)
        }

    }

Answer 1

First, create another data class based on the columns needed, then use a @Query to get the needed columns from the database or a join query for multiple tables like the one below

Query("SELECT P.purchaseID, P.itemOwnerID, P.quantity, P.soldPrice, I.itemName FROM purchases as P INNER JOIN items AS I ON I.itemID = P.itemOwnerID")
fun getAllPurchases(): Flow<List<Purchase>>

More info could be found here