How to model many-to-many relationship in Android Room with CrossRef Junction

Question

How would I model a many-to-many relationship as described in https://developer.android.com/training/data-storage/room/relationships#many-to-many, but with an additional property on the junction? I basically want to achieve the following:

@Entity
data class Playlist(
    @PrimaryKey val playlistId: Long,
    val playlistName: String
)

@Entity
data class Song(
    @PrimaryKey val songId: Long,
    val songName: String,
    val artist: String
)

@Entity(primaryKeys = ["playlistId", "songId"])
data class PlaylistSongCrossRef(
    val playlistId: Long,
    val songId: Long,
    val rating: Int // <-- the additional property
)

data class PlaylistWithRating(
    val playlist: Playlist,
    val rating: Int // <-- the additional property
)

data class SongWithPlaylists(
    @Embedded val song: Song,
    @Relation(
         parentColumn = "songId",
         entityColumn = "playlistId",
         associateBy = @Junction(PlaylistSongCrossRef::class)
    )
    val playlists: List<PlaylistWithRating>
)

so I could access it in my Dao:

@Dao
interface SongWithPlaylistsDao {
    @Query("SELECT * FROM Song")
    fun list(): LiveData<List<SongWithPlaylists>>
}

I know how, from an ERM perspective, you would model this relationship like this:

/-- A ---\      /- ACrossB -\            /-- B ---\
|        |      |           |            |        |
| - id   |----->|  - aId    |     |------| - id   |
| - name |      |  - bId    |-----|      |  -name |
|        |      |  - prop   |            |        |
\--------/      \-----------/            \--------/

I also know how to query this relationship using JOIN, but couldn't figure out from the docs how to do this in Room while keeping data integrity.

Answer 1

I ran into the same problem and finally achieved it in other way. There are cases when you can not assign an attribute to any side of the binary relationship so the attribute belongs to the relationship itself.

Step 1 -> define entity A and entity B

@Entity
data class Playlist(
    @PrimaryKey val playlistId: Long,
    val playlistName: String
)

@Entity
data class Song(
    @PrimaryKey val songId: Long,
    val songName: String,
    val artist: String
)

Step2 -> define the associative entity(CrossRef entity), and add your additional attribute to it

@Entity(primaryKeys = ["playlistId", "songId"])
data class PlaylistSongCrossRef(
    val playlistId: Long,
    val songId: Long,
    val rating: Int // <-- the additional property
)

till now it was all repeating your code :D

Step3 -> define the desired data model (Pojo) suitable to your query needs

Lets consider you want to use SongWithPlaylists model class. In SongWithPlaylists you should omit @Relation annotation from playlists and instead annotate it with @Embedded.

Note-> change playlists type from List<PlaylistWithRating> to Playlist. No need to use PlaylistWithRating, just Playlist not even a list of it. Room no longer manages join for us. we will make it :D And also add your CrossRef's additional attributes to SongWithPlaylists cause we want that additional attribute to be mapped to this object after join query finished. SongWithPlayList will be like bellow

data class SongWithPlaylists(
    @Embedded val song: Song,
    @Embedded val playlist: Playlist,
    val rating: Int
)

Step4 -> Add plain Join query to your DAO

now that you get rid of @Relation annotation and PlaylistWithRatings, you should inner join your entity A on Entity B and again join on CrossRef entity.
DAO is like bellow

@Dao
interface SongWithPlaylistsDao {
    @Query("""
        select Song.songId,Song.songName,Song.artist,Playlist.playlistId,Playlist.playlistName,PlaylistSongCrossRef.rating
        from Song inner join PlaylistSongCrossRef on Song.songId = PlaylistSongCrossRef.songId 
        inner join Playlist on Playlist.playlistId = PlaylistSongCrossRef.playlistId
        """)
    fun list(): LiveData<List<SongWithPlaylists>>
}

This was the dirty way but worked for me. Sorry for my poor English. I Hope helped you and anybody may would run into the same problem.