Sqlite SUM Entity value depending on LEFT JOIN value

I’m using Room on Android to query a POJO.

My POJO (Transaction) contains a value and an account_id. It further contains an account (instance of Account), which is fetched by a @Relation.

The transaction class:

@Parcelize
class Transaction(
    @Embedded val entity: TransactionEntity,
    @Relation(entity = Account::class, parentColumn = Database.Transactions.COL_ACCOUNT_ID, entityColumn = Database.Accounts.COL_ID) val account: Account? = null
) : Model

The accounts properties:

data class Account(
    @PrimaryKey(autoGenerate = true) @ColumnInfo(name = Database.Accounts.COL_ID) val id: Long = 0,
    @ColumnInfo(name = Database.Accounts.COL_NAME) val name: String = "",
    @ColumnInfo(name = Database.Accounts.COL_BALANCE) val balance: Double = 0.0
)

The accounts.balance is not a value in the database, but the sum of all transactions.value belonging to that account.

So I need to sum every transaction.value where transaction.account_id = account.id.

It should work similar to this way, but I’m not getting it right:

@androidx.room.Transaction
@Query(
    """
        SELECT transactions.* FROM transactions
        LEFT OUTER JOIN (SELECT transactions.value, SUM(transactions.value) AS balance FROM accounts) ON transactions.account_id = accounts.id
        GROUP BY transactions.id
"""
)
fun getTrs(): List<Transaction>

Answer

I’ve made it by creating a new POJO wrapping my original account object, but adding a field for the sum which is now queried.

@Query(
    """
        SELECT accounts.*, 
        (SELECT SUM(CASE WHEN transactions.type = ${Transaction.TransactionType.EARNING} THEN transactions.value 
        ELSE -transactions.value END) FROM transactions WHERE transactions.account_id = accounts.id) AS balance
        FROM accounts
        LEFT JOIN transactions ON transactions.account_id = accounts.id
        GROUP BY accounts.id
    """
)
fun getAccountsWithBalance(): LiveData<List<AccountWithBalance>>