kotlin - How can I represent a "many to many" relation with Android Room when column names are same?

Question

How can I represent a "many to many" relation with Room? My column names are also the same.

e.g. I have Guest and Reservation. Reservation can have many Guest's and a Guest can be part of many Reservations.

Here are my entity definitions:

@Entity data class Reservation(
    @PrimaryKey val id: Long,
    val table: String,
    val guests: List<Guest>
)

@Entity data class Guest(
    @PrimaryKey val id: Long,
    val name: String,
    val email: String
)

While looking into docs I came across @Relation. I found it really confusing though.

According to this I would want to create a POJO and add the relationships there. So, with my example I did the following:

data class ReservationForGuest(
    @Embedded val reservation: Reservation,
    @Relation(
        parentColumn = "reservation.id", 
        entityColumn = "id", 
        entity = Guest::class
    ) val guestList: List<Guest>
)

With above I get the compiler error:

> Cannot figure out how to read this field from a cursor.

I wasn't able to find a working sample of @Relation.

Answer 1

I had a similar issue. Here is my solution.

You can use an extra entity (ReservationGuest) which keeps the relation between Guest and Reservation.

@Entity data class Guest(
    @PrimaryKey val id: Long,
    val name: String,
    val email: String
)

@Entity data class Reservation(
    @PrimaryKey val id: Long,
    val table: String
)

@Entity data class ReservationGuest(
    @PrimaryKey(autoGenerate = true) val id: Long,
    val reservationId: Long,
    val guestId: Long
)

You can get reservations with their list of guestIds. (Not the guest objects)

data class ReservationWithGuests(
    @Embedded val reservation:Reservation,
    @Relation(
        parentColumn = "id",
        entityColumn = "reservationId",
        entity = ReservationGuest::class,
        projection = "guestId"
    ) val guestIdList: List<Long>
)

You can also get guests with their list of reservationIds. (Not the reservation objects)

data class GuestWithReservations(
    @Embedded val guest:Guest,
    @Relation(
        parentColumn = "id",
        entityColumn = "guestId",
        entity = ReservationGuest::class,
        projection = "reservationId"
   ) val reservationIdList: List<Long>
)

Since you can get the guestIds and reservationIds, you can query Reservation and Guest entities with those.

I'll update my answer if I find an easy way to fetch Reservation and Guest object list instead of their ids.

Similar answer