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

Answer 2

With the introduction to Junction in room you can handle many-to-many relationship with ease.

As @Devrim stated you can use an extra entity (ReservationGuest) which keeps the relation between Guest and Reservation(also know as associative table or junction table or join table).

@Entity data class Guest(   @PrimaryKey   val gId: Long,   val name: String,   val email: String )  @Entity data class Reservation(   @PrimaryKey   val rId: Long,   val table: String )  @Entity(   primaryKeys = ["reservationId", "guestId"] ) data class ReservationGuest(        val reservationId: Long,   val guestId: Long ) 

Now you can get reservation with guests using this model:

data class ReservationWithGuests (     @Embedded     val reservation: Reservation,     @Relation(             parentColumn = "rId",             entity = Guest::class,             entityColumn = "gId",             associateBy = Junction(                     value = ReservationGuest::class,                     parentColumn = "reservationId",                     entityColumn = "guestId"             )     )     val guests: List<Guest> ) 

You can also get guest with their list of reservations as.

data class GuestWithReservations (   @Embedded   val guest: Guest,   @Relation(         parentColumn = "gId",         entity = Reservation::class,         entityColumn = "rId",         associateBy = Junction(                 value = ReservationGuest::class,                 parentColumn = "guestId",                 entityColumn = "reservationId"         )   )   val reservations: List<Reservation> ) 

Now you can query database for the result as:

@Dao interface GuestReservationDao {   @Query("SELECT * FROM Reservation")   fun getReservationWithGuests(): LiveData<List<ReservationWithGuests>>    @Query("SELECT * FROM Guest")   fun getGuestWithReservations(): LiveData<List<GuestWithReservations>>  }