Many to Many relations with Room & LiveData

Question

I have a rest api that returns a list of places, which have a list of categories:

{
      "id": "35fds-45sdgk-fsd87",
      "name" : "My awesome place",
       "categories" : [
          {
            "id": "cat1",
            "name" : "Category 1"
          },
          {
            "id": "cat2",
            "name" : "Category 2"
          },
          {
            "id": "cat3",
            "name" : "Category 3"
          }
       ]
}

So using retrofit I get these from the remote server with these model classes:

data class Category(var id: String, var name: String)

data class Place(
  var id: String,
  var name: String,
  var categories: List<Category>
)

Problem is -- I want the viewModel to always retrieve from a local Room Database returning Flowables and just trigger refresh actions that will update the database and thus the view.

DAO method example:

@Query("select * from Places where placeId = :id")
fun getPlace(id: String): Flowable<Place>

So I tried modeling those two classes like this:

@Entity
data class Category(var id: String, var name: String)


@Entity
data class Place(
  @PrimaryKey
  var id: String,
  var name: String,
  var categories: List<Category>
)

But of course Room is not able to process relations on its own. I have seen this post which just retrieves from the local database the previous list of cities, but this case doesnt match that one.

Only option I could think of is to save the categories in the database as a JSON string but this is losing the relational quality of the database...

This seems like a pretty common use case but I haven't found much info about it.

Answer 1

It's possible in Room to have many to many relationship.

First add @Ignore annotation to your Place class. It will tell Room to ignore this property, because it can't save the list of objects without converter.

data class Category(
        @PrimaryKey var id: String, 
        var name: String
)

data class Place(
        @PrimaryKey var id: String,
        var name: String,
        @Ignore var categories: List<Category>
) 

Then create a class that will represent the connection between this two classes.

@Entity(primaryKeys = ["place_id", "category_id"],
        indices = [
            Index(value = ["place_id"]),
            Index(value = ["category_id"])
        ],
        foreignKeys = [
            ForeignKey(entity = Place::class,
                    parentColumns = ["id"],
                    childColumns = ["place_id"]),
            ForeignKey(entity = Category::class,
                    parentColumns = ["id"],
                    childColumns = ["category_id"])
        ])
data class CategoryPlaceJoin(
        @ColumnInfo(name = "place_id") val placeId: String,
        @ColumnInfo(name = "category_id") val categoryId: String
)

As you can see I used foreign keys.

Now you can specify special DAO for getting list of categories for a place.

@Dao
interface PlaceCategoryJoinDao {

    @SuppressWarnings(RoomWarnings.CURSOR_MISMATCH)
    @Query("""
        SELECT * FROM category INNER JOIN placeCategoryJoin ON
        category.id = placeCategoryJoin.category_id WHERE
        placeCategoryJoin.place_id = :placeId
        """)
    fun getCategoriesWithPlaceId(placeId: String): List<Category>

    @Insert
    fun insert(join: PlaceCategoryJoin)
}

And the last important thing is to insert join object each time you insert new Place.

val id = placeDao().insert(place)
for (place in place.categories) {
   val join = CategoryPlaceJoin(id, category.id)
   placeCategoryJoinDao().insert(join)
}

Now when you get places from placeDao() they have empty category list. In order to add categories you can use this part of code:

fun getPlaces(): Flowable<List<Place>> {
    return placeDao().getAll()
            .map { it.map { place -> addCategoriesToPlace(place) } }
}

private fun addCategoriesToPlace(place: Place): Place {
    place.categories = placeCategoryJoinDao().getCategoriesWithPlaceId(place.id)
    return place
}

To see more details see this article.

Answer 2

I had a similar use case. As Room doesn't manage relations, I ended up with this solution following the blog you mentioned :/

@Entity
data class Category(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,
    var catId: String, 
    var name: String,
    @ForeignKey(entity = Place::class, parentColumns = ["id"], childColumns = ["placeId"], onDelete = ForeignKey.CASCADE)
    var placeId: String = ""
)

@Entity
data class Place(
    @PrimaryKey
    var id: String,
    var name: String,
    @Ignore var categories: List<Category>
)

PlaceDao

@Dao
interface PlaceDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insert(place: Place)

    @Query("SELECT * FROM place WHERE id = :id")
    fun getPlace(id: String?): LiveData<Place>
}

fun AppDatabase.getPlace(placeId: String): LiveData<Place> {
    var placeLiveData = placeDao().getPlace(placeId)
    placeLiveData = Transformations.switchMap(placeLiveData, { place ->
        val mutableLiveData = MutableLiveData<Place>()
        Completable.fromAction { // cannot query in main thread
            place.categories = categoryDao().get(placeId)
        }
                .subscribeOn(Schedulers.io())
                .observeOn(AndroidSchedulers.mainThread())
                .subscribe { mutableLiveData.postValue(place) }
        mutableLiveData
    })
    return placeLiveData
}

// run in transaction
fun AppDatabase.insertOrReplace(place: Place) {
    placeDao().insert(place)
    place.categories?.let {
        it.forEach {
            it.placeId = place.id
        }
        categoryDao().delete(place.id)
        categoryDao().insert(it)
    }
}

CategoryDao

@Dao
interface CategoryDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insert(categories: List<Category>)

    @Query("DELETE FROM category WHERE placeId = :placeId")
    fun delete(placeId: String?)

    @Query("SELECT * FROM category WHERE placeId = :placeId")
    fun get(placeId: String?): List<Category>
}

Not a big fan but I didn't find a better way for the moment.

Answer 3

Just don't use the same class for your Entity and the Place that you fetch from network.

It's smell bad to tie your class logic with API structure.

When you're retrieving data from network, just create new Places entities and persist it to the DB.