When using an associative table (like room_group_map) then you use the associateBy parameter of the @Relation annotation to specify the Junction.

The Junction is used to define the associative table via the value parameter, the column that points to the parent (the Embedded) via the parentColumn parameter and the associated child (the Related) via the entityColumn parameter.

For your association you could, for example, have either:-

data class RoomWithAssociatedGroups(
    @Embedded
    val room: RoomEntity,
    @Relation(
        entity = GroupEntity::class,
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = RoomGroupMap::class,
            parentColumn = "room_id",
            entityColumn = "group_id"
        )
    )
    val groups: List<GroupEntity>
)

or:-

data class GroupWithAssociatedRooms(
    @Embedded
    val group: GroupEntity,
    @Relation(
        entity = RoomEntity::class,
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = RoomGroupMap::class,
            parentColumn = "group_id",
            entityColumn = "room_id"
        )
    )
    val rooms: List<RoomEntity>
)

• noting that the query will be based upon accessing the table that stores the @Embedded and that ALL associated children, irrespective of filtering, per base that is selected.

e.g.:-

@Transaction
@Query("SELECT * FROM rooms")
fun getAllRoomsWithTheRoomsGrooups(): List<RoomWithAssociatedGroups>

Will return all Rooms each with the associated group(s) if any (otherwise an empty list of Groups).

However, this table does not solve the get the room_id for a device as the table is just associating Rooms and Groups.

• You want to define other relationships, perhaps a room/device associative table, perhaps a group/device associative table. If you expect to utilise the stored list of devices then that should be via either the objects returned. Querying lists, which have to be type-converted, are not easily handled (using the relationship aspect of SQLite greatly simplifies such matters from a querying aspect)

Perhaps by saying:-

The idea was to find out the room_id for the group entity through the device entity. But I can’t figure out how to do it correctly.

You could believe that simply coding, especially the Foreign Key definitions, that Room will magically build relationships (often this thought is held). However, in SQLite (and therefore Room), a Foreign Key definition defines a constraint (rule) that enforces referential integrity. IT DOES NOT BUILD relationships, rather the rules prevent you from storing a value that cannot be a relationship.

• The ON actions can help, especially if CASCADE, to maintain the referential integrity.

Looking at the source data and the ensuing original 3 tables/@Entity annotated classes; along with the thought of related data (the associative/mapping table), then, as hinted at previously, by storing the related values as a list within the owning object is duplicating the relationship and the data. Also as stated resorting to a list within the owner (parent) is had to utilise from a database perspective. From a database perspective storing a single value in a table where that single value uniquely identifies the related row in the other table is the efficient way of managing relationships.

In short either use the lists and work with the extracted object(s) or use the databases’s (SQLite’s) powerful inherent relationship handling (e.g. associative/mapping/referential …. table(s) for many-many relationships).

• an associative/mapping etc table can handle 1 to many relationships, albeit it introducing complexity that may not be needed.

Going back to

The idea was to find out the room_id for the group entity through the device entity. But I can’t figure out how to do it correctly.

And the first part of the answer, which stresses that it does not answer this aspect. The Room-Group relationship is not going to help as there doesn’t appear to be such a relationship. Rather there is a relationship between Rooms and Devices and there is a relationship between Devices and Groups.

So it is these latter relationships that could then form part of the final solution.

As such you probably want both a Room/Device and a Group/Device mapping table e.g. :-

@Entity(
    primaryKeys = ["roomId","deviceId"]
)
data class RoomDeviceMap(
    val roomId: String,
    @ColumnInfo(index = true) /* probably more efficient to have index on 2nd column of composite primary key*/
    val deviceId: String
)

@Entity(
    primaryKeys = ["groupId","deviceId"]
)
data class GroupDeviceMap(
    val groupId: String,
    @ColumnInfo(index = true) val deviceId: String
)

• note the foreign key definitions have purposefully been omitted (to show they don’t define, or are needed for relationships).
• also note the index on the 2nd column of the composite primary key (Room will issue warning if omitted).

To utilise the above for extraction of data then you could have:-

data class RoomWithAssociatedDevices(
    @Embedded
    val room: RoomEntity,
    @Relation(
        entity = DeviceEntity::class,
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = RoomDeviceMap::class,
            parentColumn = "roomId",
            entityColumn = "deviceId"
        )
    )
    val devices: List<DeviceEntity>
)

data class DeviceWithAssociatedRooms(
    @Embedded
    val device: DeviceEntity,
    @Relation(
        entity = RoomEntity::class,
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = RoomDeviceMap::class,
            parentColumn = "deviceId",
            entityColumn = "roomId"
        )
    )
    val rooms: List<RoomEntity>
)
data class GroupWithMappedDevices(
    @Embedded
    val group: GroupEntity,
    @Relation(
        entity = DeviceEntity::class,
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = GroupDeviceMap::class,
            parentColumn = "groupId",
            entityColumn = "deviceId"
        )
    )
    val devices: List<DeviceEntity>
)

data class DeviceWithMappedGroups(
    @Embedded val device: DeviceEntity,
    @Relation(
        entity = GroupEntity::class,
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = GroupDeviceMap::class,
            parentColumn = "deviceId",
            entityColumn = "groupId"
        )
    )
    val groups: List<GroupEntity>
)

Finally, as an example, you may wish to get devices with their mapped rooms and with their mapped groups; in which case you could have:-

data class DeviceWithMappedRoomsAndMappedGroups(
    @Embedded val device: DeviceEntity,
    @Relation(
        entity = RoomEntity::class,
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = RoomDeviceMap::class,
            parentColumn = "deviceId",
            entityColumn = "roomId"
        )
    )
    val rooms: List<RoomEntity>,
    @Relation(
        entity = GroupEntity::class,
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = GroupDeviceMap::class,
            parentColumn = "deviceId",
            entityColumn = "groupId"
        )
    )
    val groups: List<GroupEntity>
)

Demo

Using the above plus the following @Dao annotated interface:-

@Dao
interface AllDAOs {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(room: RoomEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(group: GroupEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(device: DeviceEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(roomDeviceMap: RoomDeviceMap): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(roomGroupMap: RoomGroupMap): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(groupDeviceMap: GroupDeviceMap): Long

    /* not used as room/groups not populated or needed? */
    @Transaction
    @Query("SELECT * FROM rooms")
    fun getAllRoomsWithTheRoomsGroups(): List<RoomWithAssociatedGroups>
    @Transaction
    @Query("SELECT * FROM devices")
    fun getAllDevicesWithTheDevicesRooms(): List<DeviceWithAssociatedRooms>

    /* added for the new mapping tables */
    @Transaction
    @Query("SELECT * FROM rooms")
    fun getAllRoomsWithTheRoomsDevices(): List<RoomWithAssociatedDevices>
    @Transaction
    @Query("SELECT * FROM groups")
    fun getAllGroupsWithMappedDevices(): List<GroupWithMappedDevices>
    @Transaction
    @Query("SELECT * FROM devices")
    fun getAllDevicesWithMappedGroups(): List<DeviceWithMappedGroups>

    /* Finally */
    @Transaction
    @Query("SELECT * FROM devices WHERE id=:deviceId OR name=:deviceId")
    fun getADeviceWithMappedRoomsAndMappedGroups(deviceId: String): List<DeviceWithMappedRoomsAndMappedGroups>
}

So with an @Database annotated abstract class named TheDatabase (not included as it’s pretty standard as such except that .allowMainThreadQueries has been used for brevity)

Then the following activity code:-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDAOs
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        val r1 = RoomDto("RID001","ROOM 1", listOf("DID001","DID002","DID003"))
        val r2 = RoomDto("RID002","ROOM 2", listOf("DID004","DID005","DID006"))
        val g1 = GroupDto("GID001","GROUP 1", listOf("DID007","DID008"))
        val g2 = GroupDto("GID002","GROUP 2", listOf("DID009","DID0010"))
        val d001 = DeviceDto("DID001","DEVICE 1","RID001", listOf())
        val d002 = DeviceDto("DID002","DEVICE 2","RID001", listOf())
        val d003 = DeviceDto("DID003","DEVICE 3","RID001", listOf())
        val d004 = DeviceDto("DID004","DEVICE 4","RID002", listOf())
        val d005 = DeviceDto("DID005","DEVICE 5","RID002", listOf())
        val d006 = DeviceDto("DID006","DEVICE 6","RID002", listOf())
        val d007 = DeviceDto("DID007","DEVICE 7","", listOf("GID001"))
        val d008 = DeviceDto("DID008","DEVICE 8","", listOf("GID001"))
        val d009 = DeviceDto("DID009","DEVICE 9","", listOf("GID002"))
        val d010 = DeviceDto("DID010","DEVICE 10","", listOf("GID002"))

        db = TheDatabase.getInstance(this)
        dao = db.getAllDAOs()

        dao.insert(RoomEntity(r1.id,r1.name))
        dao.insert(RoomEntity(r2.id,r2.name))
        dao.insert(GroupEntity(g1.id,g1.name,"????"))
        dao.insert(GroupEntity(g2.id,g2.name,"????"))
        dao.insert(DeviceEntity(d001.id,d001.name,"????",""))
        dao.insert(DeviceEntity(d002.id,d002.name,"????",""))
        dao.insert(DeviceEntity(d003.id,d003.name,"????",""))
        dao.insert(DeviceEntity(d004.id,d004.name,"????",""))
        dao.insert(DeviceEntity(d005.id,d005.name,"????",""))
        dao.insert(DeviceEntity(d006.id,d006.name,"????",""))
        dao.insert(DeviceEntity(d007.id,d007.name,"????",""))
        dao.insert(DeviceEntity(d008.id,d008.name,"????",""))
        dao.insert(DeviceEntity(d009.id,d009.name,"????",""))
        dao.insert(DeviceEntity(d010.id,d010.name,"????",""))

        val allrooms = listOf<RoomDto>(r1,r2)
        val allgroups = listOf<GroupDto>(g1,g2)
        val alldevices = listOf<DeviceDto>(d001,d002,d003,d004,d005,d006,d007,d008,d009,d010)

        for (d in alldevices) {
            dao.insert(DeviceEntity(d.id,d.name,"whatever????",d.room))
        }
        for (r in allrooms) {
            dao.insert(RoomEntity(r.id,r.name))
        }
        for (g in allgroups) {
            dao.insert(GroupEntity(g.id,g.name,"!!!!"))
        }
        /* at this stage no relationships exist */
        for (r in allrooms) {
            for (d in r.devices) {
                dao.insert(RoomDeviceMap(r.id,d))
            }
        }
        for (g in allgroups) {
            for (d in g.devices) {
                dao.insert(GroupDeviceMap(g.id,d))
            }
        }

        /* so now the mapped data has been inserted/stored */

        for (rwad in dao.getAllRoomsWithTheRoomsDevices()) {
            val sb = StringBuilder()
            for(d in rwad.devices) {
                sb.append("ntDevice ID = ${d.id} NAME = ${d.name} TYPE = ${d.type} STOREDROOMID ${d.roomId}")
            }
            Log.d("DBINFO_1","ROOM NAME is ${rwad.room.name} ID is ${rwad.room.id}./nIt has ${rwad.devices.size} devices; they are:${sb}")
        }

        for (dwar in dao.getAllDevicesWithTheDevicesRooms()) {
            val sb = StringBuilder()
            for(r in dwar.rooms) {
                sb.append("ntROOM ID = ${r.id} NAME = ${r.name}")
            }
            Log.d("DBINFO_2","DEVICE NAME is ${dwar.device.name} ID is ${dwar.device.id} TYPE is ${dwar.device.type} " +
                    "STOREROOMID is ${dwar.device.roomId}nIt has ${dwar.rooms.size} rooms; they are:${sb}")
        }
        for (gwmd in dao.getAllGroupsWithMappedDevices()) {
            val sb = StringBuilder()
            for (d in gwmd.devices) {
                sb.append("ntDEVICE NAME is ${d.name} ID is ${d.id} TYPE is ${d.type} STOREDROOMID is ${d.roomId}")
            }
            Log.d("DBINFO_3","GROUP NAME is ${gwmd.group.name} ID is ${gwmd.group.id} TYPE is ${gwmd.group.type}" +
                    "nIt has ${gwmd.devices.size} devices: they are:-${sb}")
        }
        for (dwmg in dao.getAllDevicesWithMappedGroups()) {
            val sb = StringBuilder()
            for (g in dwmg.groups) {
                sb.append("ntGroup NAME is ${g.name} ID is ${g.id} TYPE is ${g.type}")
            }
            Log.d("DBINFO_4","DEVICE NAME IS ${dwmg.device.name} ID is ${dwmg.device.id}" +
                    "nIt has ${dwmg.groups.size} groups; they are ${sb}")
        }

        for (d in alldevices) {
            val sb1 = StringBuilder()
            val sb2 = StringBuilder()
            for (ad_etc in dao.getADeviceWithMappedRoomsAndMappedGroups(d.id)) {
                for (r in ad_etc.rooms) {
                    sb1.append("ntROOM NAME is ${r.name} ID is ${r.id}")
                }
                for (g in ad_etc.groups) {
                    sb2.append("ntGROUP NAME is ${g.name} ID is ${g.id} TYPE is ${g.type}")
                }
                Log.d("DBINFO_5","DEVICE NAME IS ${ad_etc.device.name} ID is ${ad_etc.device.id} TYPE is ${ad_etc.device.type}" +
                        "nIt has ${ad_etc.rooms.size} ROOMS; they are:${sb1}" +
                        "nIt has ${ad_etc.groups.size} groups; they are:${sb2}")
            }
        }
    }
}

When RUN, outputs the following to the log:-

2024-12-22 12:54:40.763  D/DBINFO_1: ROOM NAME is ROOM 1 ID is RID001./nIt has 3 devices; they are:
        Device ID = DID001 NAME = DEVICE 1 TYPE = ???? STOREDROOMID 
        Device ID = DID002 NAME = DEVICE 2 TYPE = ???? STOREDROOMID 
        Device ID = DID003 NAME = DEVICE 3 TYPE = ???? STOREDROOMID 
2024-12-22 12:54:40.763  D/DBINFO_1: ROOM NAME is ROOM 2 ID is RID002./nIt has 3 devices; they are:
        Device ID = DID004 NAME = DEVICE 4 TYPE = ???? STOREDROOMID 
        Device ID = DID005 NAME = DEVICE 5 TYPE = ???? STOREDROOMID 
        Device ID = DID006 NAME = DEVICE 6 TYPE = ???? STOREDROOMID 
        
        
2024-12-22 12:54:40.765  D/DBINFO_2: DEVICE NAME is DEVICE 1 ID is DID001 TYPE is ???? STOREROOMID is 
    It has 1 rooms; they are:
        ROOM ID = RID001 NAME = ROOM 1
2024-12-22 12:54:40.766  D/DBINFO_2: DEVICE NAME is DEVICE 2 ID is DID002 TYPE is ???? STOREROOMID is 
    It has 1 rooms; they are:
        ROOM ID = RID001 NAME = ROOM 1
2024-12-22 12:54:40.766  D/DBINFO_2: DEVICE NAME is DEVICE 3 ID is DID003 TYPE is ???? STOREROOMID is 
    It has 1 rooms; they are:
        ROOM ID = RID001 NAME = ROOM 1
2024-12-22 12:54:40.766  D/DBINFO_2: DEVICE NAME is DEVICE 4 ID is DID004 TYPE is ???? STOREROOMID is 
    It has 1 rooms; they are:
        ROOM ID = RID002 NAME = ROOM 2
2024-12-22 12:54:40.766  D/DBINFO_2: DEVICE NAME is DEVICE 5 ID is DID005 TYPE is ???? STOREROOMID is 
    It has 1 rooms; they are:
        ROOM ID = RID002 NAME = ROOM 2
2024-12-22 12:54:40.766  D/DBINFO_2: DEVICE NAME is DEVICE 6 ID is DID006 TYPE is ???? STOREROOMID is 
    It has 1 rooms; they are:
        ROOM ID = RID002 NAME = ROOM 2
2024-12-22 12:54:40.766  D/DBINFO_2: DEVICE NAME is DEVICE 7 ID is DID007 TYPE is ???? STOREROOMID is 
    It has 0 rooms; they are:
2024-12-22 12:54:40.766  D/DBINFO_2: DEVICE NAME is DEVICE 8 ID is DID008 TYPE is ???? STOREROOMID is 
    It has 0 rooms; they are:
2024-12-22 12:54:40.766  D/DBINFO_2: DEVICE NAME is DEVICE 9 ID is DID009 TYPE is ???? STOREROOMID is 
    It has 0 rooms; they are:
2024-12-22 12:54:40.766  D/DBINFO_2: DEVICE NAME is DEVICE 10 ID is DID010 TYPE is ???? STOREROOMID is 
    It has 0 rooms; they are:
    
    
2024-12-22 12:54:40.770  D/DBINFO_3: GROUP NAME is GROUP 1 ID is GID001 TYPE is ????
    It has 2 devices: they are:-
        DEVICE NAME is DEVICE 7 ID is DID007 TYPE is ???? STOREDROOMID is 
        DEVICE NAME is DEVICE 8 ID is DID008 TYPE is ???? STOREDROOMID is 
2024-12-22 12:54:40.771  D/DBINFO_3: GROUP NAME is GROUP 2 ID is GID002 TYPE is ????
    It has 1 devices: they are:-
        DEVICE NAME is DEVICE 9 ID is DID009 TYPE is ???? STOREDROOMID is 
        
        
2024-12-22 12:54:40.775  D/DBINFO_4: DEVICE NAME IS DEVICE 1 ID is DID001
    It has 0 groups; they are 
2024-12-22 12:54:40.776  D/DBINFO_4: DEVICE NAME IS DEVICE 2 ID is DID002
    It has 0 groups; they are 
2024-12-22 12:54:40.776  D/DBINFO_4: DEVICE NAME IS DEVICE 3 ID is DID003
    It has 0 groups; they are 
2024-12-22 12:54:40.776  D/DBINFO_4: DEVICE NAME IS DEVICE 4 ID is DID004
    It has 0 groups; they are 
2024-12-22 12:54:40.776  D/DBINFO_4: DEVICE NAME IS DEVICE 5 ID is DID005
    It has 0 groups; they are 
2024-12-22 12:54:40.776  D/DBINFO_4: DEVICE NAME IS DEVICE 6 ID is DID006
    It has 0 groups; they are 
2024-12-22 12:54:40.776  D/DBINFO_4: DEVICE NAME IS DEVICE 7 ID is DID007
    It has 1 groups; they are 
        Group NAME is GROUP 1 ID is GID001 TYPE is ????
2024-12-22 12:54:40.776  D/DBINFO_4: DEVICE NAME IS DEVICE 8 ID is DID008
    It has 1 groups; they are 
        Group NAME is GROUP 1 ID is GID001 TYPE is ????
2024-12-22 12:54:40.776  D/DBINFO_4: DEVICE NAME IS DEVICE 9 ID is DID009
    It has 1 groups; they are 
        Group NAME is GROUP 2 ID is GID002 TYPE is ????
2024-12-22 12:54:40.776  D/DBINFO_4: DEVICE NAME IS DEVICE 10 ID is DID010
    It has 0 groups; they are 
    
    
2024-12-22 12:54:40.780  D/DBINFO_5: DEVICE NAME IS DEVICE 1 ID is DID001 TYPE is ????
    It has 1 ROOMS; they are:
        ROOM NAME is ROOM 1 ID is RID001
    It has 0 groups; they are:
2024-12-22 12:54:40.789  D/DBINFO_5: DEVICE NAME IS DEVICE 2 ID is DID002 TYPE is ????
    It has 1 ROOMS; they are:
        ROOM NAME is ROOM 1 ID is RID001
    It has 0 groups; they are:
2024-12-22 12:54:40.792  D/DBINFO_5: DEVICE NAME IS DEVICE 3 ID is DID003 TYPE is ????
    It has 1 ROOMS; they are:
        ROOM NAME is ROOM 1 ID is RID001
    It has 0 groups; they are:
2024-12-22 12:54:40.797  D/DBINFO_5: DEVICE NAME IS DEVICE 4 ID is DID004 TYPE is ????
    It has 1 ROOMS; they are:
        ROOM NAME is ROOM 2 ID is RID002
    It has 0 groups; they are:
2024-12-22 12:54:40.801  D/DBINFO_5: DEVICE NAME IS DEVICE 5 ID is DID005 TYPE is ????
    It has 1 ROOMS; they are:
        ROOM NAME is ROOM 2 ID is RID002
    It has 0 groups; they are:
2024-12-22 12:54:40.808  D/DBINFO_5: DEVICE NAME IS DEVICE 6 ID is DID006 TYPE is ????
    It has 1 ROOMS; they are:
        ROOM NAME is ROOM 2 ID is RID002
    It has 0 groups; they are:
2024-12-22 12:54:40.812  D/DBINFO_5: DEVICE NAME IS DEVICE 7 ID is DID007 TYPE is ????
    It has 0 ROOMS; they are:
    It has 1 groups; they are:
        GROUP NAME is GROUP 1 ID is GID001 TYPE is ????
2024-12-22 12:54:40.815  D/DBINFO_5: DEVICE NAME IS DEVICE 8 ID is DID008 TYPE is ????
    It has 0 ROOMS; they are:
    It has 1 groups; they are:
        GROUP NAME is GROUP 1 ID is GID001 TYPE is ????
2024-12-22 12:54:40.820  D/DBINFO_5: DEVICE NAME IS DEVICE 9 ID is DID009 TYPE is ????
    It has 0 ROOMS; they are:
    It has 1 groups; they are:
        GROUP NAME is GROUP 2 ID is GID002 TYPE is ????
2024-12-22 12:54:40.824  D/DBINFO_5: DEVICE NAME IS DEVICE 10 ID is DID010 TYPE is ????
    It has 0 ROOMS; they are:
    It has 0 groups; they are: