triple join in room database using room relation

Question

I need to triple join my entities using @relation anotion in room, but I don't know how.

here is my summary of entities:

@Entity(tableName = "session_table")
data class Session(
    @PrimaryKey(autoGenerate = true)
    var sessionId: Long = 0L,

    @ColumnInfo(name = "lesson_id")
    var lessonId: Long
)

@Entity(tableName = "lessons_table")
data class Lesson(
    @PrimaryKey(autoGenerate = true)
    val lessonId: Long,

    @ColumnInfo(name = "teacher_id")
    var teacherId: Long = -1L
)

@Entity(tableName = "teacher_table")
data class Teacher(
    @PrimaryKey(autoGenerate = true)
    val teacherId: Long = 0L
)

I assume the answer would be something like this:

data class SessionWithLessonWithTeacher(
    @Embedded
    val session: Session,

    @Relation(
        parentColumn = "lesson_id",
        entityColumn = "lessonId"
    )
    var lesson: Lesson,

    @Relation(
        parentColumn = "teacher_id", // this is the teacher id in lesson
        entityColumn = "teacherId",
    )
    var teacher: Teacher
)

MikeT · Answer 1 · 2021-08-13T22:42:47.550

Your guess at what SessionWithlessonWithTeacher should be won't work because it is effectively saying get the teacher_id from the Session. There is no teacher_id in a Session.

To use @Relation's then you need to follow the hierarchy. A Session has a Lesson and a Lesson has a Teacher. So in Session you need to get a Lesson with a Teacher.

As such have (working up though the hierarchy) :-

data class LessonWithTeacher(
    @Embedded
    val lesson: Lesson,
    @Relation(
        entity = Teacher::class,
        parentColumn = "teacher_id",
        entityColumn = "teacherId")
    val teacher: Teacher
)

and

data class SessionWithLessonWithTeacher(
    @Embedded
    val session: Session,
    @Relation(
        entity = Lesson::class, /* NOTE Lesson  NOT LessonWithTeacher (not a table) */
        parentColumn = "lesson_id",
        entityColumn = "lessonId")
    val lessonWithTeacher: LessonWithTeacher
)

You would use an @Dao such as :-

@Query("SELECT * FROM session_table")
@Transaction
abstract fun getSessionWithLessonWithTeacher(): List<SessionWithLessonWithTeacher>

Alternative Approach

The way that Room uses @Relation is that it initially only retrieves the Embedded object, it then retrieves all of the @Related objects via subsequent queries and hence why it warns(expects) @Transaction.

For your scenario, where there will be 1 lesson per session and 1 teacher per lesson, you can embed all three and have a single query (albeit it more complicated) that JOIN's the three tables.

So instead of (or as well as) SessionWithLessonWithTeacher you could have:-

data class SessionLessonTeacher(
    @Embedded
    val session: Session,
    @Embedded
    val lesson: Lesson,
    @Embedded
    val teacher: Teacher
)

note @Embedded can be a pain if column names aren't unique between the embedded objects

The equivalent query would/could be :-

@Query("SELECT * FROM session_table " +
        "JOIN lessons_table ON lessons_table.lessonId = session_table.lesson_id " +
        "JOIN teacher_table ON teacher_table.teacherId = lessons_table.teacher_id")
abstract fun getSessionLessonTeacher(): List<SessionLessonTeacher>

note that in your case the table.column could just be column as the column names are all unique.

triple join in room database using room relation

1 Answers1