How To Add Embedded And Relation To Room Database

Let's say I have these tables : Table - User Stores the users @Entity( tableName = 'USER' ) data class User( @PrimaryKey(autoGenerate = true) @ColumnInfo(name = 'user

Solution 1:

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

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):

dataclassSpecialityDetails( 
    @Embedded
    val special: Special, 
    @Embedded
    val item: Item,
    val favourite: Boolean
)

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>

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

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

)