CODEWITHSUNDEEP
androidkotlinandroid-room
Rickless
Rickless

Reputation: 1455

Android Room Kotlin inner join

I have two entities Accounts:

@Entity(tableName = "accounts",foreignKeys = arrayOf(
        ForeignKey(
                entity = Currency::class,
                parentColumns = arrayOf("id"),
                childColumns = arrayOf("currencyId"),
                onDelete = ForeignKey.CASCADE
        )
))
data class Account (
        @PrimaryKey(autoGenerate = true)
        var id:Int=0,
        @ColumnInfo(name="name")
        var accountName:String,
        @ColumnInfo(name = "balance")
        var initialBalance:Double,
        var currencyId:Int,
        var date:Date,
        var isDefault:Boolean=true
){
    constructor():this(0,"",0.0,0,Date(),false)
}

And Currencies:

@Entity(tableName = "currencies")
data class Currency(
        @PrimaryKey(autoGenerate = true)
        var id:Int=0,
        @ColumnInfo(name="name")
        var currencyName:String,
        @ColumnInfo(name="code")
        var currencyCode:String
)
{
    constructor():this(0,"","")

    override fun toString(): String =currencyCode
}

I want to embed a currency object in account. As you can see, I have one-to-many relationship between currencies and accounts. When I query the accounts entity I want to view its currency too. I tried adding an @Embedded field in account entity but it doesn't work obviously there is something I'm misunderstanding , the field is returned with null "No exception just null". And if possible to "flatten" the currency object inside the account object, this would be much better.

The point of all of this is, I want to display all accounts in RecyclerView with their currencies information. I'm now confused between @Embedded and @Relation any help would be much appreciated.

Edit
I don't know if this might help:
This is my AccountDao:

@Dao
interface AccountDao {
    @Insert
    fun insertAll(items:Array<Account>)

    @Update
    fun update(item:Account)

    @Delete
    fun delete(item:Account)

    @Query("select * from accounts")
    fun getAll():LiveData<Array<Account>>
}

Upvotes: 6

Views: 3392

Answers (2)

Akshay Chordiya
Akshay Chordiya

Reputation: 4841

I won't recommend the above method because you end up writing the same properties (repeating yourself) aka boilerplate code.

Use the @Embedded and Relation annotation in the following way and your code will most probably look like this:

data class AccountWithCurrency (
    @Embedded
    var account: Account? = null,
    @Relation(parentColumn = "id", entityColumn = "currencyId")
    var currency: List<Currency>? = null,
){
constructor() : this(Account(), emptyList())
}

Upvotes: 4

Rickless
Rickless

Reputation: 1455

I managed to get it to work. The solution was to create a separate (POJO or POKO whatever) class, I called it AccountModel:

class AccountModel{
    var accountId:Int = 0
    var accountName:String = ""
    var accountInitialBalance:Double = 0.0
    var accountCreationDate: Date = Date()
    var currencyId:Int = 0
    var currencyCode:String = ""
    var isDefaultAccount:Boolean = false
    constructor()
    constructor(id:Int,name:String,balance:Double,date:Date,currencyId:Int,currencyCode:String,isDefault:Boolean){
        this.accountId = id
        this.accountName = name
        this.accountInitialBalance = balance
        this.accountCreationDate = date
        this.currencyId = currencyId
        this.currencyCode = currencyCode
        this.isDefaultAccount= isDefault
    }
    fun toAccount():Account = Account(this.accountId,this.accountName,this.accountInitialBalance,this.currencyId,this.accountCreationDate,this.isDefaultAccount)
}

And then, constructing the query to do a normal inner join as if you are doing inner join for ordinary SQL database. Like this:

@Query("select accounts.id as accountId," +
            "accounts.name as accountName," +
            "accounts.balance as accountInitialBalance," +
            "accounts.currencyId," +
            "accounts.date as accountCreationDate," +
            "accounts.isDefault as isDefaultAccount," +
            "currencies.code as currencyCode " +
            "from accounts inner join currencies on accounts.currencyId=currencies.id")
    fun getAll():LiveData<Array<AccountModel>>

Apparently, you can use as x to project this column to a x field in the returned object, as you can tell, in the database the column is accounts.id but in my AccountModel it is a accountId.
And the really impressive thing by Google Room is that I was able to get a LiveData of AccountModel even though I'm adding an Account object which is really clever.

Upvotes: 2

Related Questions