Room insert into one-to-many relationship

Question

When trying to use intermediary classes to model entity relationships in Room, I have run into an issue. Whilst documentation describes how to get from a one-to-many relationship, it does not describe how to insert.

I'm assuming this cannot be done automatically, therefore we need a query to insert the parent, retrieve the ID for the parent and assign it to the child's foreign key, and then insert the child.

The problem is that I am unsure where to put such a query. If I include it in my DAO, then I will have to include superflous methods for inserting the child. If I include it in my Repository, this makes testing very difficult (if not impossible).

Does anyone know how to resolve this?

MikeT · Accepted Answer · 2021-12-12T15:46:12.003

I'm assuming this cannot be done automatically, therefore we need a query to insert the parent, retrieve the ID for the parent and assign it to the child's foreign key, and then insert the child.

The first assumption is correct, that is that you have to supply the id of the parent (otherwise how is to know the parent).

However the second assumption that you have to query the parent is not always the case and not so in the scenario you describe. If when inserting a parent then the id is returned if using the convenience @Insert as a Long or an array of Longs (if inserting multiple Parents).

For example, say you have :-

@Entity
data class Parent(
    @PrimaryKey
    var id: Long? = null,
    var other: String
)

and

@Entity
data class Child(
    @PrimaryKey
    var id: Long? = null,
    var parentId: Long,
    var otherdata: String
)

and an @Dao annotated class with :-

@Insert
fun insert(parent: Parent): Long
@Insert
fun insert(child: Child): Long

Then you can use the following, without having to query the Parent:-

    var lastParent = dao.insert(Parent(other = "Parent1 other data"))
    dao.insert(Child(parentId = lastParent, otherdata = "Child1 other data"))
    dao.insert(Child(parentId = lastParent, otherdata = "Child2 other data"))

    // Insert a Child with it's Parent together
    dao.insert(Child(
        parentId = dao.insert(Parent(other = "Parent2 other data")),
        otherdata = "Child3 other data"
    ))

note even if you define the id's as Int, a Long is returned when inserting.
- It is incorrect to use Int for an id as SQLite stores the id as a 64bit signed integer which is to large for an Int.
  - However, issues would not occur until the id reached a value that is too large for an Int (32bit signed) i.e. greater than 2,147,483,647.

Room insert into one-to-many relationship

1 Answers1