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

Issue

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

    }

Solution

Here i have tried to create join query Make Sure itemId in product and itemOwnerId is not null

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

Answered By - Ankita Patel