Question or issue of Kotlin Programming:
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
)
@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
)
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.
How to solve this issue?
Solution no. 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
)
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
)
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
Solution no. 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
)
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
)
Now you can query database for the result as:
@Dao
interface GuestReservationDao {
@Query("SELECT * FROM Reservation")
fun getReservationWithGuests(): LiveData>
@Query("SELECT * FROM Guest")
fun getGuestWithReservations(): LiveData>
}
Solution no. 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
}
To see more details visit this blog.
Solution no. 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> 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 tables;
@TypeConverter
public List fromGroupConcat(String reservations) {
return Arrays.asList(reservations.split(","));
}
}
Solution no. 5:
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>
data class GuestWithReservations(
var id: Long? = null,
var name: String? = null,
var email: String? = null,
var reservations: List = emptyList()
)
class Converters{
@TypeConverter
fun listReservationFromConcatString(value: String?): List? = 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.
Solution no. 6:
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
)
So at this point you can use val guestIdList: List because your ReservationGuest entity actually maps ids with entity models.
