Android Room persistence library — Relations advanced example

If you are reading this article you might have some familiarity with Room library, so I will skip the fancy intro where I talk about this fantastic software component.😅 Let’s dive in real world problems! 😱

But wait! Take a look at this guide for a better comprehension of this article (which does not talk about relations at all).

https://codelabs.developers.google.com/codelabs/android-room-with-a-view-kotlin/#0

We all know…relations are difficult! Let’s say that we have 2 entities: trip and place. So we have 2 tables, one is for storing all our trips and one for storing all possible places that we can visit. In addition there is a relationship between travel and trip, in fact a trip can have a starting place and a place of arrival. 🧐

Let’s start from something simple, the place entity!

Place.kt

@Entity
data class Place(
        @PrimaryKey(autoGenerate = true) val placeId: Int,
        @ColumnInfo(name = "iconId") val iconId: Int?,
        @ColumnInfo(name = "name") val name: String?,
        @ColumnInfo(name = "city") val city: String?
)

PlaceDao.kt

@Dao
interface PlaceDao {
    @Query("SELECT * FROM place")
    fun getAll(): LiveData<List<Place>>

    @Insert
    fun insertAll(vararg places: Place)

    @Query("DELETE FROM place")
    fun deleteAll()
}

Now something more difficult! 😩 Don’t worry, it’s a simply class that contains only data. But this sounds like an obvious answer! There a 3 important things that you have to meditate on:

1. @ForeignKey annotation is very helpful here to keep those tables in relationship, it’s a bonding between starting place and place of arrival and 2 places in the table called “Places”.
2. @Entity annotation refers to Trip data class, this means Room will create a table according to this class. Pay attention that the class TripPopulated is not an entity, we don’t want to create another table!😇 TripPopulated is needed to retrieve our trips with populated values for fromPlace and toPlace fields.
3. @Embedded annotation is near 2 properties in TripPopulated: fromPlace and toPlace. By this way we tell Room that TripPopulated class has all Place properites (placeId, iconId, name and city) in it twice times: one for starting place and one for place of arrival! In fact the second one is prefixed by “to_” (so the properties will be called to_placeId, to_iconId, to_name and to_city). Remember column names must be unique, that’s why we need the prefix…🤓

Trip.kt

@Entity
data class Trip(
        @PrimaryKey(autoGenerate = true) val tripId: Int,

        @ColumnInfo(name = "fromDate") val fromDate: Date,
        @ForeignKey(entity = Place::class, parentColumns = ["placeId"], childColumns = ["fromPlace"], onDelete = ForeignKey.CASCADE)
        @ColumnInfo(name = "fromPlace") val fromPlace: Int,

        @ColumnInfo(name = "toDate") val toDate: Date,
        @ForeignKey(entity = Place::class, parentColumns = ["placeId"], childColumns = ["toPlace"], onDelete = ForeignKey.CASCADE)
        @ColumnInfo(name = "toPlace") val toPlace: Int
)

data class TripPopulated(val tripId: Int,
                                  val fromDate: Date,
                                  @Embedded val fromPlace: Place,
                                  val toDate: Date,
                                  @Embedded(prefix = "to_") val toPlace: Place)

Now comes the most difficult part: understanding the SQL query!!!🤯 insertAll query is not difficult to understand, the one upper is a bit harder. getAllPopulated short query 🤨 (I was joking!) returns a list of trips with populated values for fromPlace and toPlace thanks to 2 inner join operations, but the interesting thing happens after the SELECT keyword! I was not able to but simply SELECT * because Room couldn’t understand how to populated toPlace value. 🤬 Remember when I prefixed toPlace property in TripPopulated class? I had no choice, so now we have to prefix manually each field of place02 joined table and BOOM! Everything is working now! 😲

TripDao.kt

@Dao
interface TripDao {
    @Query("SELECT t.*, place01.*, " +
            "place02.placeId as to_placeId, place02.city as to_city, place02.name as to_name, place02.iconId as to_iconId " +
            "FROM trip as t INNER JOIN place as place01 ON fromPlace = place01.placeId INNER JOIN place as place02 on toPlace = place02.placeId")//
    fun getAllPopulated(): LiveData<List<TripPopulated>>

    @Insert
    fun insertAll(vararg luggageTravels: LuggageTravel)
}

Hope that this little article was helpful, happy coding!