如何将嵌入式和关联添加到会议室数据库 [英] How to add Embedded and Relation to room database

问题描述

假设我有这些表:

存储用户

@Entity(
    tableName = "USER"
)
data class User(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "user_id")
    val id: Int,

    @ColumnInfo(name = "user_name")
    val name: String
)

表格-项目

像商品一样存储商品

Store the items it's like a product

@Entity(
    tableName = "ITEM"
)
data class Item(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "item_id")
    val id: Int,

    @ColumnInfo(name = "item_name")
    val name: String,

    @ColumnInfo(name = "item_description")
    val description: String
)

表格-特殊

存储产品的特色产品1特色产品需要存在

Store an speciality for product 1 Special needs a Product to exist

@Entity(
    tableName = "SPECIAL",
    foreignKeys = [ForeignKey(
        entity = Item::class,
        parentColumns = ["item_id"],
        childColumns = ["special_item_id"]
    )]
)
data class Special(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "special_id")
    val id: Int,

    @ColumnInfo(name = "special_item_id")
    val coupon_product_id: Int,

    @ColumnInfo(name = "special_name")
    val name: String,

    @ColumnInfo(name = "special_description")
    val description: String

)

表格-收藏夹

存储用户喜欢的特价商品

Stores the favourite Specials from an user

@Entity(
    tableName = "TB_FAVOURITE",
    foreignKeys = [ForeignKey(
        entity = User::class,
        parentColumns = ["user_id"],
        childColumns = ["favourite_user_id"]
    ), ForeignKey(
        entity = Special::class,
        parentColumns = ["special_id"],
        childColumns = ["favourite_special_id"]
    )]
)
data class Favourite(
    @PrimaryKey
    @ColumnInfo(name = "favourite_user_id")
    val id: Int,

    @ColumnInfo(name = "favourite_special_id")
    val specialId: Int

)

我的问题是，如何查询以选择所有的Specials，然后创建类似于存储用户是否喜欢的类的类.目前，这是一个用户应用程序，是一个演示应用程序.因此，用户永远是相同的，因此我可以对findById进行硬编码并发送用户的ID.

My question is, how can I make a query to select all the Specials and then creating like a class that stores if it's favourite of the user or not. It's one user app for the moment, it's for a demo app. So, user will always be the same so I can hardcode the findById and send the id of the user.

是否以包含以下内容的List作为查询结果:

Is to get a result of a query as List that contains :

1. 所有特价商品
2. 在SomeClass中应在其中包含特殊项目
3. 一个标志，以了解它是否是该驱动程序的最爱

问题是我希望能够将会议室数据库的结果映射到所需的对象，因此我想查询比映射器更重要，我知道该怎么做.

The thing is I want to be able to map the result of the room database to my desired object, so I guess the query is more important than the mapper, I know how to do the mapper.

查询将如何执行此操作? db的结构是否有任何改进以使其更容易?

How would be the query to do that? Is there any improvement of the structure of the db to make it easier?

推荐答案

我想查询比映射器更重要，我知道该怎么做.

I guess the query is more important than the mapper, I know how to do the mapper.

好吧，查询取决于您想要获得的结果的结构. 在这种情况下，我不认为@Relation会有所帮助(表之间的关系太复杂了)，所以我建议对JOIN使用自定义查询.

Well, query depends on the structure of the result you want to get. I don't think @Relation could help in that case (relations between tables are too complicated for that), so I suggest to use custom query with JOINs.

我的建议是添加一些具有您需要的结构的类(尽管您可以更改它):

My suggestion is to add some class with structure you need to get (though you can change it):

data class SpecialityDetails( 
    @Embedded
    val special: Special, 
    @Embedded
    val item: Item,
    val favourite: Boolean
)

然后在DAO中添加下一个方法(据我所知，您可以在此方法中传递userId):

And in DAO add next method (as I've understood you can pass userId in this method):

@Query("select *, CASE WHEN tb_favourite.favourite_user_id is null THEN 0 else 1 END as favourite from special 
INNER JOIN ITEM ON special.special_item_id = ITEM.item_id 
LEFT JOIN tb_favourite ON special.special_id = tb_favourite.favourite_special_id 
AND tb_favourite.favourite_user_id = :userId")

fun getSpecials(userId: Int): List<SpecialityDetails>

由于SpecialityDetails包含查询中的所有字段，因此客房将为您进行映射.

Room will do mapping for you since SpecialityDetails includes all fields that are in the query.

更新 为您的Favourite类使用复合主键

Update Use composite primary key for your Favourite class

@Entity(
    tableName = "TB_FAVOURITE",
    primaryKeys = arrayOf("favourite_user_id", "favourite_special_id"),
    foreignKeys = [ForeignKey(
        entity = User::class,
        parentColumns = ["user_id"],
        childColumns = ["favourite_user_id"]
    ), ForeignKey(
        entity = Special::class,
        parentColumns = ["special_id"],
        childColumns = ["favourite_special_id"]
    )]
)
data class Favourite(
    @ColumnInfo(name = "favourite_user_id")
    val id: Int,

    @ColumnInfo(name = "favourite_special_id")
    val specialId: Int

)

这篇关于如何将嵌入式和关联添加到会议室数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！