Define and query nested relationships

Sometimes, you might need to query a set of three or more tables that are all related to each other. In that case, you define nested relationships between the tables.

Suppose that in the music streaming app example, you want to query all the users, all the playlists for each user, and all the songs in each playlist for each user. Users have a one-to-many relationship with playlists, and playlists have a many-to-many relationship with songs. The following code example shows the classes that represent these entities as well as the cross-reference table for the many-to-many relationship between playlists and songs:

@Entity
dataclassUser(
@PrimaryKeyvaluserId:Long,
valname:String,
valage:Int
)
@Entity
dataclassPlaylist(
@PrimaryKeyvalplaylistId:Long,
valuserCreatorId:Long,
valplaylistName:String
)
@Entity
dataclassSong(
@PrimaryKeyvalsongId:Long,
valsongName:String,
valartist:String
)
@Entity(primaryKeys=["playlistId","songId"],indices=[Index("playlistId","songId")])
dataclassPlaylistSongCrossRef(
valplaylistId:Long,
valsongId:Long
)
NestedRelationshipsSnippets.kt 

First, model the relationship between two of the tables in your set as you normally do, using a data class and the @Relation annotation. The following example shows a PlaylistWithSongs class that models a many-to-many relationship between the Playlist entity class and the Song entity class:

dataclassPlaylistWithSongs(
@Embeddedvalplaylist:Playlist,
@Relation(
parentColumns=["playlistId"],
entityColumns=["songId"],
associateBy=Junction(PlaylistSongCrossRef::class)
)
valsongs:List<Song>
)
NestedRelationshipsSnippets.kt 

After you define a data class that represents this relationship, create another data class that models the relationship between another table from your set and the first relationship class, "nesting" the existing relationship within the new one. The following example shows a UserWithPlaylistsAndSongs class that models a one-to-many relationship between the User entity class and the PlaylistWithSongs relationship class:

dataclassUserWithPlaylistsAndSongs(
@Embeddedvaluser:User,
@Relation(
entity=Playlist::class,
parentColumns=["userId"],
entityColumns=["userCreatorId"]
)
valplaylists:List<PlaylistWithSongs>
)
NestedRelationshipsSnippets.kt 

The UserWithPlaylistsAndSongs class indirectly models the relationships between all three of the entity classes: User, Playlist, and Song. This is illustrated in figure 1.

UserWithPlaylistsAndSongs models the relationship between User and PlaylistWithSongs, which in turn models the relationship between Playlist and Song.

If your set has more tables, create a class to model the relationship between each remaining table and the previous relationship class. This process creates a chain of nested relationships among all tables you want to query.

Finally, add a function to the data access object (DAO) class to expose the query function that your app needs. This function requires Room to run multiple queries, so add the @Transaction annotation so that the whole operation runs atomically:

@Transaction
@Query("SELECT * FROM User")
suspendfungetUsersWithPlaylistsAndSongs():List<UserWithPlaylistsAndSongs>
NestedRelationshipsSnippets.kt