How to join three tables in ROOM

Question

I need to join three Entities

1.

@Entity(tableName = "accounts") data class AccountModel(
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,
    var name: String = "name",
    var currency: String = "currency",
    var number: String? = null,
    var color: Int = 0,
    var value: Double = 0.0,
    var start_value: Double = 0.0,
    var note: String? = null,
    var accountType: String? = null)

@Entity(tableName = "categories")data class CategoryModel (
    @PrimaryKey(autoGenerate = true)
    var id: Int? = null,
    var name: String,
    var color: Int,
    var is_income: Boolean
)

@Entity(tableName = "transactions")
data class TransactionModel(
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,
    var name: String? = null,
    var date: Long = 0L,
    var value: Double = 0.0,
    var account_id: Int? = null,          <-
    var category_id: Int? = null,         <-
    var description: String? = null,
    var is_income: Boolean = true
)

What type of relation is it? I can do one-to-one. Is it one-to-many? Should I use Junction?

Answer 1

It's one-to-one relation. You can add next class:

data class TransactionWithAccountAndCategory(
    @Embedded val transaction: Transaction,
    @Relation(
         parentColumn = "account_id",
         entityColumn = "id"
    )
    val account: AccountModel
    @Relation(
         parentColumn = "category_id",
         entityColumn = "id"
    )
    val category: CategoryModel
)

and get it with next method:

@Transaction
@Query("SELECT * FROM transactions")
fun getTransactions(): List<TransactionWithAccountAndCategory>

So you don't have to use Junction, because it's needed only for many-to-many relations with additional table (and according to your entities you don't have such a case).