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>>

}

Answer 3

Actually there is one more possibility to get Guest list, not only id's like in @Devrim answer.

First define class which will represent the connection between Guest and Reservation.

@Entity(primaryKeys = ["reservationId", "guestId"],
        foreignKeys = [
            ForeignKey(entity = Reservation::class,
                    parentColumns = ["id"],
                    childColumns = ["reservationId"]),
            ForeignKey(entity = Guest::class,
                    parentColumns = ["id"],
                    childColumns = ["guestId"])
        ])
data class ReservationGuestJoin(
    val reservationId: Long,
    val guestId: Long
)

Each time you will be inserting new Reservation, you will have to insert ReservationGuestJoin object in order to fulfill foreign key constraint. And now if you want to get Guest list you can use power of SQL query:

@Dao
interface ReservationGuestJoinDao {

    @SuppressWarnings(RoomWarnings.CURSOR_MISMATCH)
    @Query("""
        SELECT * FROM guest INNER JOIN reservationGuestJoin ON
        guest.id = reservationGuestJoin.guestId WHERE
        reservationGuestJoin.reservationId = :reservationId
        """)
    fun getGuestsWithReservationId(reservationId: Long): List<Guest>
}

To see more details visit this blog.

Answer 4

Here is a way to query a full object model through an M:N junction table in a single query. The subqueries are probably not the most efficient way to do this, but it does work until they get @Relation to properly walk through ForeignKey. I hand-jammed the Guest/Reservation framework into my working code so there may be typos.

Entity (This has been covered)

@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
)

Dao (Note we pull in the M:N via a subquery and reduce the extra Reservation rows with a GROUP_CONCAT

@Query("SELECT *, " +
            "(SELECT GROUP_CONCAT(table) " +
                "FROM ReservationGuest " +
                "JOIN Reservation " +
                "ON Reservation.id = ReservationGuest.reservationId " +
                "WHERE ReservationGuest.guestId = Guest.id) AS tables, " +
        "FROM guest")
abstract LiveData<List<GuestResult>> getGuests();

GuestResult (This handles the mapping of the query result, note we convert the concatenated string back to a list with @TypeConverter)

@TypeConverters({ReservationResult.class})
public class GuestResult extends Guest {
    public List<String> tables;

    @TypeConverter
    public List<String> fromGroupConcat(String reservations) {
        return Arrays.asList(reservations.split(","));
    }
}

Answer 5

Based on the answer above: https://stackoverflow.com/a/44428451/4992598 only by keeping separate field names between entities you can have models returned (not just ids). All you need to do is:

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

And yes entities can be embedded in one another as long as you don't keep duplicate fields. So in consequence the ReservationWithGuests class can look like this.

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

So at this point you can use val guestIdList: List because your ReservationGuest entity actually maps ids with entity models.

Answer 6

For the join table entity, I suggest to use a composite ID indexed:

@Entity(
    primaryKeys = ["reservationId", "guestId"],
    indices = [Index(value =["reservationId", "guestId"], unique = true)]
)
data class ReservationGuestJoin(
    @PrimaryKey(autoGenerate = true) var id: Long,
    var reservationId: Long = 0,
    var guestId: Long = 0
)

The GuestDao.kt:

@Dao
@TypeConverters(GuestDao.Converters::class)
interface GuestDao {

    @Query(QUERY_STRING)
    fun listWithReservations(): LiveData<List<GuestWithReservations>>

    data class GuestWithReservations(
        var id: Long? = null,
        var name: String? = null,
        var email: String? = null,
        var reservations: List<Reservation> = emptyList()
    )

    class Converters{
        @TypeConverter
        fun listReservationFromConcatString(value: String?): List<Reservation>? = value?.let { value ->
                .split("^^")
                .map { it.split("^_") }
                .map { Reservation(id = it.getOrNull(0)?.toLongOrNull(), name = it.getOrNull(1)) }
        } ?: emptyList()
    }
}

The QUERY_STRING. We make a inner joins to produce a big table with data from both entities, them we concatenate the data from Reservation as a column string and lastly we group_concat the rows by the guest ID, concatenating the reservation strings with different separators, our converter will take care of rebuild it as a entity:

SELECT 
    t.id, t.name, t.email, GROUP_CONCAT(t.reservation, '^^') as reservations 
FROM (
    SELECT 
        guestId as id, name, email, (reservationId || '^_' || reservationTable) as reservation 
    FROM  
        GuestReservationJoin
        INNER JOIN Guest ON Guest.id = GuestReservationJoin.guestId 
        INNER JOIN Reservation ON Reservation.id = GuestReservationJoin.reservationId
    ) as t 
GROUP BY t.id

Note that I changed your column table name because I think Room do not allow you to use SQLite reserved names.

I didn't test the performance of all this compared with have more flat entity (another option without the concatenations). If I do, I'll update my answer.