How to map multiple embedded tables in room database?

Question

I was using room but now struggling to find this type of mapping.

@Entity(tableName = "avatars")
data class AvatarEntity(
    @PrimaryKey val id: Int,
    @ColumnInfo(name = "user_id") var userId: Int,
    var avatar: String
)

@Entity(tableName = "cities")
data class CityEntity(
    @PrimaryKey val id: Int,
    val name: String
)

@Entity(tableName = "driver_cities")
data class DriverCityEntity (
    @PrimaryKey val id: Int,
    @ColumnInfo(name = "user_id") var userId : String,
    @ColumnInfo(name = "city_id") var cityId : String
)

@Entity(tableName = "drivers")
data class DriverEntity(
    @PrimaryKey val id: Int,
    @ColumnInfo(name = "full_name") var name: String
)

First avatars table will contain an avatar info of driver.

cities table contains a list of cities.

driver_cities table will hold a reference to driver and the its city_id.

drivers table will contain data of drivers.

Now i want to create a separate object, which will contains a driver and city of that driver, also avatar of that driver.

Just like as shown in following code.

data class DriverWithAvatarAndCity(
    val driver: DriverEntity,
    val avatar: AvatarEntity,
    val city: CityEntity
)

if i use @Embedded and do a relation with avatar, because avatar table has user_id column, so i can get the avatar, but for city, the city_id is in separate table somehow we need the driver_city_entity as embedded here.

Well i'm totally stuck here, please guide.

Answer 1

Assuming that a driver has 1 avatar only and that a driver can operate in multiple cities (i.e. DriverCityEntity implies many-many) then.

For a Driver you can embed (@Embedded) the driver and get the avatar with a simple relationship (@Relation) the Cities can be obtained via the driver city mapping table using a more complex relationship (@Relation). That is in Room your associate (associateBy) the Driver with the City via the mapping table ( a Junction) e.g. :-

data class DriverWithAvatarAndCity(
    @Embedded val driver: DriverEntity,

    @Relation(
        entity = CityEntity::class,
        parentColumn = "id", // The parent is the driver
        entityColumn = "id", // The child is the City(ies)
        // map (associate) the driver to the city(ies) via :-
        associateBy = Junction(
            DriverCityEntity::class, // The mapping table
            parentColumn = "user_id", // The column in the mapping table that maps the Driver
            entityColumn = "city_id" // The column in the mapping table that masps the City
        )
    )
    val city: List<CityEntity>,
    @Relation(entity = AvatarEntity::class, parentColumn = "id", entityColumn = "user_id")
    val avatar: AvatarEntity
)

You can then use the above in a query (@Dao) such as :-

@Query("SELECT * FROM drivers")
@Transaction
abstract fun getDriverWithCitiesAndDriverAvatar(): List<DriverWithAvatarAndCity>

Example

With AllDao as :-

@Dao
abstract class AllDao {
    @Insert
    abstract fun insert(avatarEntity: AvatarEntity): Long
    @Insert
    abstract fun insert(cityEntity: CityEntity): Long
    @Insert
    abstract fun insert(driverEntity: DriverEntity): Long
    @Insert
    abstract fun insert(driverCityEntity: DriverCityEntity): Long
    @Query("SELECT * FROM drivers")
    @Transaction
    abstract fun getDriverWithCitiesAndDriverAvatar(): List<DriverWithAvatarAndCity>
}

using the following :-

class MainActivity : AppCompatActivity() {

    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    private val TAG = "DRVINFO"
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()

        // Adds some testing data
        var londonId = dao.insert(CityEntity(1,"London"))
        var parisId = dao.insert(CityEntity(2,"Paris"))
        var bonnId = dao.insert(CityEntity(5,"Bonn"))
        var amsertdamId = dao.insert(CityEntity(9,"Amsterdam"))

        var d1 = dao.insert(DriverEntity(10,"Mary"))
        var d2 = dao.insert(DriverEntity(1,"Fred"))

        var a1 = dao.insert(AvatarEntity(1,d1,"Mary Avatar"))
        var a2 = dao.insert(AvatarEntity(2,d2,"Fred Avatar"))

        // link/map driver and cities
        dao.insert(DriverCityEntity(1000,d1,londonId))
        dao.insert(DriverCityEntity(1001,d1,bonnId))
        dao.insert(DriverCityEntity(1002,d2,amsertdamId))

        // Get all the Drivers, with the driver's avatar and the list of cities
        for(dwa: DriverWithAvatarAndCity in dao.getDriverWithCitiesAndDriverAvatar()) {
            Log.d(TAG,"Driver is ${dwa.driver.name} avatar is ${dwa.avatar.avatar}")
            for (c: CityEntity in dwa.city) {
                Log.d(TAG,"\tCity is ${c.name}")
            }
        }
    }
}

Results in the following being included in the log:-

D/DRVINFO: Driver is Fred avatar is Fred Avatar
D/DRVINFO:  City is Amsterdam
D/DRVINFO: Driver is Mary avatar is Mary Avatar
D/DRVINFO:  City is London
D/DRVINFO:  City is Bonn