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_id")
    val id: Int,

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

Table - Item

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

Table - Special

>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

)

Table -- Favourite

>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

)

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.

The goal

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

1. All of the Specials
2. In the SomeClass should contain the Item of the Special inside
3. A flag to know if it's favourite for that driver or not

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.

Note that I'm using an assets/database/mydb.db file to start the database I don't know if it matters, so I do not know how would work using Embedded and Relation here

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.

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
)

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

)