How to use Room Relation annotations to JOIN several objects at once?

user_14514464 0
Viewed 47

Recently learned about such a thing as Relation annotation in Room, but immediately came to a dead end from misunderstanding. I have the following database model:

DatabaseCategory

@Entity(tableName = "categories")
data class DatabaseCategory(
    @PrimaryKey
    val id: Int = 0,
    val name: String ="",
    val description: String = "",
    @ColumnInfo(name = "picture_url") val pictureUrl: String = "",
)

DatabaseProduct

@Entity(tableName = "products",
    foreignKeys = [
        ForeignKey(entity = DatabaseCategory::class,
            parentColumns = arrayOf("id"),
            childColumns = arrayOf("category_id"),
            onDelete = ForeignKey.CASCADE
        )],
    indices = [
        Index(value = ["category_id"])]
)
data class DatabaseProduct(
    @PrimaryKey
    val id: Long = 0L,
    val name: String,
    val price: Float,
    val discount: Float = 0f,
    val description: String = "",
    @ColumnInfo(name = "category_id") val categoryId: Int,
    @ColumnInfo(name = "picture_url") val pictureUrl: String,
    val recent: OffsetDateTime? = null
)

DatabaseCartItem

@Entity(tableName = "cart",
    foreignKeys = [
        ForeignKey(entity = DatabaseProduct::class,
            parentColumns = arrayOf("id"),
            childColumns = arrayOf("product_id"),
            onDelete = ForeignKey.CASCADE
        )],
    indices = [
        Index(value = ["product_id"])])
data class DatabaseCartItem(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,
    @ColumnInfo(name = "product_id")
    val productId: Long,
    var quantity: Int = 0
)

I figured out how to use this tool in the simplest case.

data class DatabaseCategoryWithProducts(
    @Embedded val category: DatabaseCategory,
    @Relation(
        parentColumn = "id",
        entityColumn = "category_id"
    )
    val products: List<DatabaseProduct>
)

The question is: how to specify an object for Room and DAO query so that I can get such an object

data class DomainCartItem(
    val id: Long,
    val product: Product,
    val category: Category,
    val quantity: Int
)
android-room kotlin android sql
1 Answers

For what you want try next object:

data class DomainCartItem(
    @Embedded val product: DatabaseProduct, // <-- here you get your id and quantity
    
    @Relation(
        parentColumn = "category_id",
        entityColumn = "id"
    )
    val category:DatabaseCategory,
    @Relation(
        parentColumn = "id",
        entityColumn = "productId"
    )
    val cartItem:DatabaseCartItem,
)

and dao method:

@Transaction
@Query("SELECT * FROM products")
fun getDomainCartItems(): List<DomainCartItem>

UPDATE

To get only products that are contained as CartItems you can use another query:

@Transaction
@Query("SELECT * FROM products JOIN cart on products.id = cart.productId")
fun getDomainCartItems(): List<DomainCartItem>
user_127184140
Related