Android room one to many and compute values

Question

I have a one to many relation in room. I need to fetch list of data and calculate one property on every object as sum of amounts. Can i write a query for that? or i must iterate over the values in the code?

Update

@Entity(tableName = "account_table")
class AccountModel(
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,
    var name: String,
    var bankNumber: String,
    var amount: Double
)


@Entity(tableName = "spent_table")
data class SpentModel(
    var amount: Double,
    var title: String,
    var description: String,
    var date: String?,
    var accountId: Int,
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,
)

data class AccountWithSpent(
    @Embedded val account: AccountModel,
    @Relation(
        parentColumn = "id",
        entityColumn = "accountId"
    )
    val spent: List<SpentModel>
)

AccountWithSpent is one to many relation of my data and i want get all amounts from spent and store it to account object amount property.

Answer 1

Can i write a query for that?

Yes

or i must iterate over the values in the code?

No, but you could.

Note although perhaps useful content you may wish to go straight to the bottom and read the Additional/Correction section.

Assuming that you want to get the sum of the amounts spent when retrieving one or more AccountWithSpent objects, then you could include a val/field for this in AccountWithSpent e.g. :-

data class AccountWithSpent(
    @Embedded val account: AccountModel,
    @Relation(
        parentColumn = "id",
        entityColumn = "accountId"
    )
    val spent: List<SpentModel>,
    val sumOfSpent: Double /*<<<<< ADDED for SUM*/
)

You could then have a query such as :-

@Transaction
@Query("SELECT *, (SELECT sum(spent_table.amount)  FROM spent_table WHERE spent_table.accountId = a.id) AS sumOfSpent   FROM account_table AS a")
    fun getAccountWithSpent(): List<AccountWithSpent>

• AS sumOfSpent is important, it names that column from which the data is to be obtained to be the same name of the field in the POJO, they MUST match.
• AS a disambiguates the id column when it is used in the WHERE clause of the subquery ((SELECT sum(spent_table.amount) FROM spent_table WHERE spent_table.accountId = a.id))
  • i.e. both the account_table and the spent_table have an id column. So using a.id enforces the use of the account_table id.

You could also have a query such as :-

@Transaction
@Query("SELECT account_table.*, sum(spent_table.amount) AS sumOfSpent FROM account_table JOIN spent_table ON account_table.id = spent_table.accountId GROUP BY account_table.id")
fun getAccountWithSpentV2(): List<AccountWithSpent>

• This doesn't use a subquery but instead JOIN's the tables and uses GROUP BY to group rows into accounts.
• To suppress the unused columns (from the spent_table), which Room issues a warning about, account_table.* is used to define the output columns (i.e. only columns from the account table)
  • again Room builds the underlying Spent objects from the accounts

Alternative

If you only wanted the Account with the Sum and not the List of the underlying Spent objects then you could use the following:-

data class AccountWithSumOfSpent(
    @Embedded
    val account: AccountModel,
    val sumOfSpent: Double
)

with :-

@Query("SELECT *, sum(spent_table.amount) AS sumOfSpent FROM account_table JOIN spent_table ON account_table.id = spent_table.accountId GROUP BY account_table.id")
fun getAccountWithSumofSpent(): List<AccountWithSumOfSpent>

• Note @Transaction is not required as all data is extracted by the single query

  • the subquery is embedded in the main query. However, when using @Relation Room builds and executes queries in addition to the main query and hence why it warns about using @Transaction

Working Example using both methods

    /* ADD 1 Account with 3 Spends (totalling $100.00) */
    val account1Id = dao.insert(AccountModel(0,"BANKX","000000",0.00)).toInt()
    dao.insert(SpentModel(30.00,"SPEND1","BUYA","2021-06-25",account1Id))
    dao.insert(SpentModel(30.00,"SPEND2","BUYB","2021-06-24",account1Id))
    dao.insert(SpentModel(40.00,"SPEND3","BUYC","2021-06-23",account1Id))

    /* Extract and Log using first method (Account, with Sum and with all Spends) */
    for(a: AccountWithSpent in dao.getAccountWithSpent()) {
        Log.d("ACCNTINFO","Account Name is ${a.account.name} Bank Number is ${a.account.bankNumber} SUM of Spend is ${a.sumOfSpent}")
        //Log.d("ACCNTINFO","Account Name is ${a.account.name} Bank Number is ${a.account.bankNumber}")
        for(s: SpentModel in a.spent) {
            Log.d("ACCNTINFO","Title is \t${s.title} Description is ${s.description} Date is ${s.date} Amount is ${s.amount}")
        }
    }
    /* Extract and Log using second method (no Spends) */
    for(a: AccountWithSumOfSpent in dao.getAccountWithSumofSpent()) {
        Log.d("ACCNTINFO", "Account Name is ${a.account.name} Bank Number is ${a.account.bankNumber} SUM of Spend is ${a.sumOfSpent}")
    }

Result

The Log includes :-

2021-06-26 07:58:55.265 D/ACCNTINFO: Account Name is BANKX Bank Number is 000000 SUM of Spend is 100.0
2021-06-26 07:58:55.265 D/ACCNTINFO: Title is   SPEND1 Description is BUYA Date is 2021-06-25 Amount is 30.0
2021-06-26 07:58:55.265 D/ACCNTINFO: Title is   SPEND2 Description is BUYB Date is 2021-06-24 Amount is 30.0
2021-06-26 07:58:55.265 D/ACCNTINFO: Title is   SPEND3 Description is BUYC Date is 2021-06-23 Amount is 40.0


2021-06-26 07:58:55.268 D/ACCNTINFO: Account Name is BANKX Bank Number is 000000 SUM of Spend is 100.0

Additional/Correction

I need to fetch list of data and calculate one property on every object as sum of amounts. AccountWithSpent is one to many relation of my data and i want get all amounts from spent and store it to account object amount property.

I didn't properly read the above, so based upon your original AccountWithSpent then you could use:-

@Transaction
@Query("SELECT account_table.id,account_table.name, account_table.bankNumber, sum(spent_table.amount) AS amount FROM account_table JOIN spent_table ON account_table.id = spent_table.accountId GROUP BY account_table.id")
fun getAccountWithSpent(): List<AccountWithSpent>

and the AccountModel's amount will be populated accordingly.

However, it should be noted that the amount in the database will still be what it was, it will not change. A rhetorical question, do you need the amount to be saved in the database as it can always be calculated?

Perhaps have AccountModel have:-

.....
@Ignore 
var amount: Double

If you want the amount in the database then perhaps you should ALWAYS maintain the amount in the database. If so then you may wish to consider using a TRIGGER(s) whenever a Spend is added/deleted or updated to adjust the amount automatically (if so then that would have to be another question).