I am trying to create a relationship between 3 tables that are a one-to-many in sequence with each other. So Table1 has a many-to-one to Table 2 & Table2 has a many-to-one to Table3.
What I'm trying to do is create a relationship so I can have Table3 along with a list of related Table2 entities and another list of Table1 entities that are related to Table2. I've been looking over the documentation and questions on here but the relations I've tried either give me exceptions or are missing entities or provided entities that are not related to what I am querying for.
If this is not possible the way I am trying could you guide me in the right direction where I should be taking this? What I have currently is a transaction that queries up the first relation (Table3-Table2) and then the second based on the first entities (Table2-Table1) and returning an object of all of these relationships.
Please let me know if there's already been a question/blog post that answers this because I have failed to find it.
You have to approach this in a hierarchical way (bottom up)
You have a POJO for Table2 (@Embedded) with a List of Table3 children (@Relation)
You then have a POJO for Table1 (@Embedded) with a list of the Table2 POJO children (@Relation).
e.g. :-
Table1
@Entity(tableName = "table1")
data class Table1(
@PrimaryKey
@ColumnInfo(name = "table1_id")
val id: Long? = null,
@ColumnInfo(name = "table1_name")
val name: String
)
Table2
@Entity(
tableName = "table2",
foreignKeys = [
ForeignKey(
entity = Table1::class,
parentColumns = ["table1_id"],
childColumns = ["map_to_parent_in_table1"],
onDelete = CASCADE,
onUpdate = CASCADE
)
]
)
data class Table2(
@PrimaryKey
@ColumnInfo(name = "table2_id")
val id: Long? = null,
@ColumnInfo(name = "map_to_parent_in_table1", index = true)
val map_to_parent: Long,
@ColumnInfo(name = "table2_name")
val name: String
)
Table3
@Entity(
tableName = "table3",
foreignKeys = [
ForeignKey(
entity = Table2::class,
parentColumns = ["table2_id"],
childColumns = ["map_to_parent_in_table2"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
)
]
)
data class Table3(
@PrimaryKey
@ColumnInfo(name = "table3_id")
val id: Long? = null,
@ColumnInfo(name = "map_to_parent_in_table2", index = true)
val map_to_parent: Long,
@ColumnInfo(name = "table3_name")
val name: String
)
foreignKeys are optional but enforce and help maintain referential integrity. Within foreign keys the onDelete and onUpdate are optionalSo starting at the lowest (youngest sibling with children i.e. Table2) then POJO
Table2WithTable3Children
data class Table2WithTable3Children(
@Embedded
val table2: Table2,
@Relation(
entity = Table3::class,
parentColumn = "table2_id",
entityColumn = "map_to_parent_in_table2"
)
val children: List<Table3>
)
then POJO
Table1WithTable2ChildrenIncludingTable3Children
data class Table1WithTable2ChildrenIncludingTable3Children(
@Embedded
val table1: Table1,
@Relation(
entity = Table2::class,
parentColumn = "table1_id",
entityColumn = "map_to_parent_in_table1"
)
val children: List<Table2WithTable3Children>
)
So if you get a Table1WithTable2ChildrenIncludingTable3Children then it includes a list of Table2WithTable3Children.
You could have a query such as :-
@Query("SELECT * FROM table1" )
@Transaction
abstract fun getAllTable1sWithTable2ChildrenAndTable3Children(): List<Table1WithTable2ChildrenIncludingTable3Children>
This could be used like:-
dao.getAllTable1sWithTable2ChildrenAndTable3Children()
for(t1: Table1WithTable2ChildrenIncludingTable3Children in dao.getAllTable1sWithTable2ChildrenAndTable3Children()) {
Log.d(TAG,"Table1 is ${t1.table1.name} ID is ${t1.table1.id} it has ${t1.children.size} children in Table2 they are:-")
for(t2: Table2WithTable3Children in t1.children) {
Log.d(TAG,"\tTable2 is ${t2.table2.name} ID is ${t2.table2.id} it has ${t2.children} in Table3 they are :-")
for(t3: Table3 in t2.children) {
Log.d(TAG,"\t\tTable3 is ${t3.name} ID is ${t3.id}")
}
}
}
Working Example/Demo
Expanding upon the above with an @Dao annotated class AllDao (this includes the query above) :-
@Dao
abstract class AllDao {
@Insert
abstract fun insert(table1: Table1): Long
@Insert
abstract fun insert(table2: Table2): Long
@Insert
abstract fun insert(table3: Table3): Long
@Query("SELECT * FROM table1" )
@Transaction
abstract fun getAllTable1sWithTable2ChildrenAndTable3Children(): List<Table1WithTable2ChildrenIncludingTable3Children>
}
and a basic @Database annotated class ThisDatabase :-
@Database(entities = [Table1::class,Table2::class,Table3::class], exportSchema = false, version = 1)
abstract class ThisDatabase: RoomDatabase() {
abstract fun getAllDao(): AllDao
companion object {
private var instance: ThisDatabase? = null
fun getInstance(context: Context): ThisDatabase {
if (instance == null) {
instance = Room.databaseBuilder(context,ThisDatabase::class.java,"this_database.db")
.allowMainThreadQueries()
.build()
}
return instance as ThisDatabase
}
}
}
And to test it all out MainActivity :-
class MainActivity : AppCompatActivity() {
lateinit var db: ThisDatabase
lateinit var dao: AllDao
val TAG = "DBINFO"
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = ThisDatabase.getInstance(this)
dao = db.getAllDao()
val t1_1 = dao.insert(Table1( name = "First Table1"))
val t1_2 = dao.insert(Table1(name = "Second Table1"))
val t1_3 = dao.insert(Table1(name = "Third Table1"))
val t2_1 = dao.insert(Table2(name = "First Table2 with First Table1 as Parent", map_to_parent = t1_1))
val t2_2 = dao.insert(Table2(name = "Second Table2 with First Table1 as Parent", map_to_parent = t1_1))
val t2_3 = dao.insert(Table2(name = "Third Table2 with Second Table1 as Parent", map_to_parent = t1_2))
dao.insert(Table3(name = "T31", map_to_parent = t2_1))
dao.insert(Table3(name = "T32", map_to_parent = t2_2))
dao.insert(Table3(name = "T33", map_to_parent = t2_3))
dao.insert(Table3(name = "T34", map_to_parent = t2_1))
dao.insert(Table3(name = "T35", map_to_parent = t2_1))
dao.getAllTable1sWithTable2ChildrenAndTable3Children()
for(t1: Table1WithTable2ChildrenIncludingTable3Children in dao.getAllTable1sWithTable2ChildrenAndTable3Children()) {
Log.d(TAG,"Table1 is ${t1.table1.name} ID is ${t1.table1.id} it has ${t1.children.size} children in Table2 they are:-")
for(t2: Table2WithTable3Children in t1.children) {
Log.d(TAG,"\tTable2 is ${t2.table2.name} ID is ${t2.table2.id} it has ${t2.children.size} in Table3 they are :-")
for(t3: Table3 in t2.children) {
Log.d(TAG,"\t\tTable3 is ${t3.name} ID is ${t3.id}")
}
}
}
}
}
Then when run for the first time the Result written to the log is :-
D/DBINFO: Table1 is First Table1 ID is 1 it has 2 children in Table2 they are:-
D/DBINFO: Table2 is First Table2 with First Table1 as Parent ID is 1 it has 3 in Table3 they are :-
D/DBINFO: Table3 is T31 ID is 1
D/DBINFO: Table3 is T34 ID is 4
D/DBINFO: Table3 is T35 ID is 5
D/DBINFO: Table2 is Second Table2 with First Table1 as Parent ID is 2 it has 1 in Table3 they are :-
D/DBINFO: Table3 is T32 ID is 2
D/DBINFO: Table1 is Second Table1 ID is 2 it has 1 children in Table2 they are:-
D/DBINFO: Table2 is Third Table2 with Second Table1 as Parent ID is 3 it has 1 in Table3 they are :-
D/DBINFO: Table3 is T33 ID is 3
D/DBINFO: Table1 is Third Table1 ID is 3 it has 0 children in Table2 they are:-