At the moment I am receiving data like this from the server:
@Serializable
data class RoomDto(
val id: String,
val name: String,
val devices: List<String>
)
@Serializable
data class GroupDto(
val id: String,
val name: String,
val devices: List<String>
)
@Serializable
data class DeviceDto(
val id: String,
val name: String,
val room: String,
val groups: List<String>
)
Created this type of entity:
@Entity(tableName = "rooms")
data class RoomEntity(
@PrimaryKey val id: String,
val name: String,
@ColumnInfo("device_ids") val deviceIds: List<String>
)
@Entity(tableName = "groups")
data class GroupEntity(
@PrimaryKey val id: String,
val name: String,
val type: String,
@ColumnInfo("device_ids") val deviceIds: List<String>
)
@Entity(tableName = "devices")
data class DeviceEntity(
@PrimaryKey val id: String,
val name: String,
val type: String,
@ColumnInfo("room_id") val roomId: String,
@ColumnInfo("group_ids") val groupIds: List<String>
)
Now I need to get data like this:
data class RoomWithDevices(
@Embedded
val room: RoomEntity,
@Relation(...)
val groups: List<GroupEntity> = emptyList(),
@Relation(...)
val devices: List<DeviceEntity> = emptyList()
)
I read the forums and understand that you need to create a table with associations. In the end I ended up with something like this:
@Entity(
tableName = "room_group_map",
foreignKeys = [
ForeignKey(
entity = RoomEntity::class,
parentColumns = ["id"],
childColumns = ["room_id"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
),
ForeignKey(
entity = GroupEntity::class,
parentColumns = ["id"],
childColumns = ["group_id"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
)
],
primaryKeys = ["room_id", "group_id"]
)
data class RoomGroupMap(
@ColumnInfo("room_id") val roomId: String,
@ColumnInfo("group_id") val groupId: String
)
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.
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>
)
@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.
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.
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).
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
)
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("\n\tDevice 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("\n\tROOM 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("\n\tDEVICE 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("\n\tGroup 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("\n\tROOM NAME is ${r.name} ID is ${r.id}")
}
for (g in ad_etc.groups) {
sb2.append("\n\tGROUP 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: