Room database with one-to-one relation

Question

I have 2 Entities, Coin and CoinRevenue.

Basically, coin holds the price in USD for some other currency.

For example, Coin with symbol EUR with value of 1.0356

@Entity(tableName = "coin")
data class Coin(
        @field:PrimaryKey(autoGenerate = false)
        var id: String = "",
        var symbol: String = "",
        var pricInUsd: Float = 0f)

CoinRevenue is an Entity that I use to hold how much coins of that specific coins the User have. For example, CoinRevenue has relation to Coin Entity with EUR symbol and amount of 1000.

@Entity(tableName = "coinRevenue")
    data class CoinRevenueNew(
            @field:PrimaryKey(autoGenerate = true)
            var id: Int = 0,
            var coin: Coin? = null,
            var amount: Float = 0f)

Now I want to fetch CoinRevenue from the database and get the updated Coin from the database.

for example, i saved the Coin with (EUR,1.0253) and than Saved a CoinRevenue with that coin.

After that I updated the Coin with (EUR,2.522) I want that the Coin object inside CoinRevenue will be updated as well.

I understand that @Embedded just add the inner objet fields as colums to the same parent object. and when I use relation, I have to use a List or a Set. but I always have 1 Coin inside CoinRevenue.

My coinDAO:

@Query("select * from coin order by rank")
fun getAllCoins(): Flowable<List<CoinDB>>

@Query("select * from coin where rank = 1")
fun getFirstCoin(): Maybe<CoinDB>

@Query("select * from coin where favourite = 1 order by rank")
fun getAllFavouriteCoins(): Flowable<List<CoinDB>>

@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertCoin(coinDB: CoinDB)

@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertCoins(coinsList: List<CoinDB>)

// -----------------
// CoinRevenue
// -----------------

@Query("select * from coinRevenue order by rank")
fun getAllCoinsRevenue(): Flowable<List<CoinRevenue>>

@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertCoinRevenue(coinRevenue: CoinRevenue)

@Delete()
fun deleteCoinRevenue(coinRevenue: CoinRevenue)

What is the best way to creat this?

Solution 1

So after a lot of tries, I've managed to get it working.

I Changed the CoinRevenue object to hold a foreign key to the Coin id

@Entity(tableName = "coinRevenue", foreignKeys = (arrayOf(ForeignKey(
        entity = CoinDB::class,
        onUpdate = ForeignKey.CASCADE,
        parentColumns = arrayOf("coinId"),
        childColumns = arrayOf("coinDbId"))))
)
data class CoinRevenue(
        @ColumnInfo(name = "mid")
        @PrimaryKey(autoGenerate = true)
        var id: Long = 0L,
        @ColumnInfo(name = "coinDbId")
        var coinDbId: String? = null,
        @ColumnInfo(name = "amount")
        var amount: Double = 0.toDouble()
)

I needed to create a POJO with both objects, like that:

class CoinRevenueWithCoin() : Parcelable {
@Embedded lateinit var coinDB: CoinDB
@Embedded lateinit var coinRevenue: CoinRevenue
}

and the query it like this:

@Query("select * from coinRevenue, coin where coinRevenue.coinDbId = coin.coinId order by coin.rank")
fun getAllCoinsRevenueWithCoin(): Flowable<List<CoinRevenueWithCoin>>

That's it.

In addition this query, as any other regular objects query, emit objects if there is any change in the 'coin' table or the 'coinRevenue' table

Solution 2

Your solution has several major drawback. One of them is that the tables' columns has to have different names. Instead of using @embededed I suggest to apply @Relation.

@Entity(tableName = "coin")
data class Coin(
        @field:PrimaryKey(autoGenerate = false)
        var id: String = "",
        var symbol: String = "",
        var pricInUsd: Float = 0f)

@Entity(tableName = "coinRevenue", foreignKeys = (arrayOf(ForeignKey(
        entity = CoinDB::class,
        onUpdate = ForeignKey.CASCADE,
        parentColumns = arrayOf("coinId"),
        childColumns = arrayOf("coinDbId"))))
)
data class CoinRevenue(
        @ColumnInfo(name = "mid")
        @PrimaryKey(autoGenerate = true)
        var id: Long = 0L,
        @ColumnInfo(name = "coinDbId")
        var coinDbId: String? = null,
        @ColumnInfo(name = "amount")
        var amount: Double = 0.toDouble()
) 

I am not familiar with Kotlin so the solution is in Java

class CoinRevenueExt extends CoinRevenue {
        @Relation(parentColumn = "coinDbId", entityColumn = "coinId" ) 
        List<Coin> coins;

        public Coin getCoin() {
            return coins.get(0);
        }

}

And Dao is simple as that

@Query("select * from coinRevenue")
public Flowable<List<CoinRevenueExt>> getAllCoinsRevenueWithCoin();

Comments

[hara]

@AhmedHegazy, @Relation seems to me not the proper solution to the one-to-one relation problem. The documentation says The type of the field annotated with Relation must be a List or Set. so it doesn't fit very well with a one-to-one relation. Looking at Yigit Boyar introduction to Room, it seems to me that the best solution would be to create a third POJO class.

[Junior Usca]

Can you give me more information about how to use relation, the DAO is simple but what columns contains? Please explain me more

[sim]

@JuniorUsca google docs are pretty good in describing Relation developer.android.com/reference/android/arch/persistence/roo‌​m/…

[Leandro Ocampo]

you can use embededed with prefix instead of worrying about column with same name.

[Leandro Ocampo]

it seems using prefix will cause this issue: stackoverflow.com/questions/48536865/…