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
becausemy way has to do with reading all items from the database
then comparingpurchase.itemOwnerID
toitem.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)
}
}
CodePudding user response:
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>>