Room database query for several Entities

Question

Could you please help me? I try to get data from TMDB via Retrofit2 to Room database and display it in Recyclerview. I make 3 api calls to get data about popular movie id/poster/overview/title/genres, with the second call I get movie duration by movie id, and with the 3rd call I get movie cast by movie id.

@GET("movie/popular")
suspend fun getPopularMovies(@Query("api_key") apiKey: String?): PopularResponseModel

@GET("/movie/{movie_id}")
suspend fun getMovieDuration(@Query("api_key") apiKey: String?): MovieDuration

@GET("/movie/{movie_id}/credits")
suspend fun getCrewAndCast(@Query("api_key") apiKey: String?): Cast

And I get 3 models:

@Entity(tableName = "movie")
data class MovieResponse(
    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "movie_id")
    val id: Int,
    @ColumnInfo(name = "movie_image")
    val poster_path: String,
    @ColumnInfo(name = "movie_overview")
    val overview: String,
    @ColumnInfo(name = "movie_title")
    val title: String,
    @ColumnInfo(name = "movie_genres")
    val genre_ids: ArrayList<Int>)

@Entity(tableName = "movie_duration")
data class MovieDuration(
    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "movie_id")
    val id: Int,
    @ColumnInfo(name = "duration")
    val runtime: Int
)

@Entity(tableName = "cast")
data class Cast(
    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "movie_id")
    val id : Int,
    val cast :ArrayList<Cast>
)

The first question is how do I make a @Query request (in the @Dao interface) that will help me to display the popular movie name, title, genre + movie duration associated with that movie in a Recyclerview. I guess they should be mapped somehow by movie id...

For example (John Wick - Action/crime, 2h 10m).

The second question is how to make a @Query request that will help me to associate the exact movie I click on with the movie cast?

(movie John Wick - John Wick: Keanu Reeves...)

Answer 1

The first question is how do I make a @Query request (in the @Dao interface) that will help me to display the popular movie name, title, genre + movie duration associated with that movie in a Recyclerview. I guess they should be mapped somehow by movie id...

As there is a one to one relationship between movie and it's duration then you could simplify matters by including the duration field in the movie. For example instead of MovieResponse and MovieDuration have just MovieResponse as:-

@Entity(tableName = "movie")
data class MovieResponse(
    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "movie_id")
    val id: Int,
    @ColumnInfo(name = "movie_image")
    val poster_path: String,
    @ColumnInfo(name = "movie_overview")
    val overview: String,
    @ColumnInfo(name = "movie_title")
    val title: String,
    @ColumnInfo(name = "movie_genres")
    val genre_ids: ArrayList<Int>,
    @ColumnInfo(name = "duration")
    val runtime: Int
)

Otherwise you would typically have a POJO class that reflects the two clasess/entities/tables that has two fields; one a MovieResponse and the other a MovieDuration.

e.g.

Option 1 (via @Embedded annotation):-

data class MovieWithEmbeddedDuration(
    @Embedded
    val movieResponse: MovieResponse,
    @Embedded(prefix = "duration_") /* prefix required to disambiguate movie_id column that is in both */
    val movieDuration: MovieDuration
)

• This would require the use of a JOIN such as @Query("SELECT movie.*, movie_duration.movie_id AS duration_movie_id, movie_duration.duration AS duration_duration FROM movie JOIN movie_duration ON movie.movie_id = movie_duration.movie_id") fun getMoviesWithEmbeddedDuration(): List<MovieWithEmbeddedDuration>
  • note the complexity due to the need to disambiguate the movie_id column which appears in both tables. AS is used to alter the name of the column when it is output (much easier to have unique column names, as the movie_id is a reference/map/relation to the parent perhaps name the column movie_id_map which better describes the column's usage).

Option 2 (via @Relation annotation)

data class MovieWithRelatedDuration(
    @Embedded
    val movieResponse: MovieResponse,
    @Relation(
        entity = MovieDuration::class,
        parentColumn = "movie_id",
        entityColumn = "movie_id"
    )
    val movieDuration: MovieDuration
)

• this doesn't require the join you simple use a query to get the parent(s) BUT get the POJO with @relation e.g. @Transaction @Query("SELECT * FROM movie") fun getMoviesWithRelatedDuration(): List<MovieWithRelatedDuration>
• Here @Relation does the work BUT it works by running separate queries to get the children (mimicking the JOIN), hence why the @Transaction annotation (not needed but warned if omitted). Thus this is less efficient.

Option 2 (via field for the duration)

Instead of embedding the MovieDuration object, you could extract just the duration and have a field for that e.g.

data class MovieWithDurationAsField(
    @Embedded
    val movieResponse: MovieResponse,
    val duration: Int
)

• This could be used using @Query("SELECT movie.*,duration FROM movie JOIN movie_duration ON movie_duration.movie_id = movie.movie_id") fun getMoviesWithDurationAsField(): List<MovieWithDurationAsField>

• A JOIN is used so this is efficient not that the value for the field is determined according to the column that matches it's name. So if runtime were used then you'd have to rename the output column from duration to runtime.

The second question is how to make a @Query request that will help me to associate the exact movie I click on with the movie cast?

This has basically been explained above (see Option 2). Create the POJO with the parent @Embedded and the children with @Relation annotation and the val being a List/Array of the child objects rather than a single object.

e.g. something like:-

data class MovieWithCastList(
    @Embedded
    val movieResponse: MovieResponse,
    @Relation(
        entity = Cast::class,
        parentColumn = "movie_id",
        entityColumn = "movie_id"
    )
    val castList: List<Cast>
)

• The Query/function would be along the lines of @Query("SELECT * FROM movie") fun getMovieWithCastList(): List<MovieWithCastList>

• Note obviously with Retrofit you adapt the queries accordingly.