Query n to m SQlite database

Question

I have n to m database (movie and cast). I need to get a list of cast associated with a movie.

@Entity(tableName = "movie")
data class MovieDbModel(
   @PrimaryKey(autoGenerate = false)
   val id: Int,
   val poster_path: String,
   val overview: String,
   val title: String)

@Entity(tableName = "cast")
@TypeConverters(CastConverter::class)
data class CastDbModel(
   @PrimaryKey(autoGenerate = false)
   val id : Int,
   val cast: Cast //Arraylist of casts
)
data class Cast(
   @Embedded
   var name: String,
   var profile_path: String?,
   var character: String
)

Crossreferenced class:

@Entity(
    tableName = "movie_cast",
    primaryKeys = ["movieIdMap","castIdMap"],
    foreignKeys = [
        ForeignKey(
            entity = MovieDbModel::class,
            parentColumns = ["id"],
            childColumns = ["movieIdMap"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = CastDbModel::class,
            parentColumns = ["id"],
            childColumns = ["castIdMap"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class MovieCastCrossRef(
    var movieIdMap: Int,
    @ColumnInfo(index = true)
    var castIdMap: Int
)

Relation:

data class MovieWithListOfCast(
    @Embedded /* The parent */
    var movie: MovieDbModel,
    @Relation(
        entity = CastDbModel::class, 
        parentColumn = "id",
        entityColumn = "id", 
        associateBy = Junction(
            value = MovieCastCrossRef::class, 
            parentColumn = "castIdMap", 
            entityColumn = "movieIdMap" 
        )
    )
    var castList: List<CastDbModel>
)

The Query looks like this:

    @Transaction
    @Query("select * FROM `cast` WHERE id = :id")
    fun getAllCastAssociatedWithMovie(id: Int): List<MovieWithListOfCast>

But I get the following warning:

The query returns some columns [cast] which are not used by MovieWithListOfCast. You can use @ColumnInfo annotation on the fields to specify the mapping. You can annotate the method with @RewriteQueriesToDropUnusedColumns to direct Room to rewrite your query to avoid fetching unused columns. MovieWithListOfCast has some fields [poster_path, overview, title] which are not returned by the query. If they are not supposed to be read from the result, you can mark them with @Ignore annotation. You can suppress this warning by annotating the method with @SuppressWarnings(RoomWarnings.CURSOR_MISMATCH). Columns returned by the query: id, cast.

How do I query to get a list of Cast that contains name, profile_path, and character?

Answer 1

I believe that you have concepts reversed.

First,

the @Embedded is the parent thus the parentColumn should specify a column in the @Embedded, you have parentColumn = "castIdMap" when it should be parentColumn = "movieIdMap" along with changing to use entityColumn = "castIdMap".

• This would not necessarily be apparent at first but would likely lead to confusion (all depends upon the reversed id's)

Second,

the MovieWithListOfCast will get the list of cast for a movie, therefore it needs to get the movie from the movie table not the cast table.

So MovieWithListOfClass could be:-

data class MovieWithListOfCast(
    @Embedded /* The parent */
    var movie: MovieDbModel,
    @Relation(
        entity = CastDbModel::class,
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = MovieCastCrossRef::class,
            parentColumn = "movieIdMap",
            entityColumn = "castIdMap"
        )
    )
    var castList: List<CastDbModel>
)

Along with:-

@Transaction
@Query("select * FROM `movie` WHERE id = :id")
fun getAllCastAssociatedWithMovie(id: Int): List<MovieWithListOfCast>

You could also have (just in case that is what you are thinking):-

data class CastWithListOfMovies(
    @Embedded
    var castList: CastDbModel,
    @Relation(
        entity = MovieDbModel::class,
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = MovieCastCrossRef::class,
            parentColumn = "castIdMap",
            entityColumn = "movieIdMap"
        )
    )
    var movieList: List<MovieDbModel>
)

Along with:-

@Transaction
@Query("select * FROM `cast` WHERE id = :id")
fun getAllMoviesAssociatedWithCast(id: Int): List<CastWithListOfMovies>

Putting both into action using your code amended as above and with a suitable @Database annotated class then using:-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()

        val m1id = dao.insert(MovieDbModel(100,"posetr001","Western","The Good, the Bad and the Ugly"))
        val m2id = dao.insert(MovieDbModel(200,"poster002","Sci-Fi","Star Wars"))
        val m3id = dao.insert(MovieDbModel(300,"poster003","Soppy","Gone with the Wind"))

        val c1id = dao.insert(CastDbModel(1,Cast("Actor1","acts","an actor")))
        val c2id = dao.insert(CastDbModel(2,Cast("Actor2","acts","an actor")))
        val c3id = dao.insert(CastDbModel(3,Cast("Actor3","acts","an actor")))
        val c4id = dao.insert(CastDbModel(4,Cast("Actor4","acts","an actor")))
        val c5id = dao.insert(CastDbModel(5,Cast("Actor5","acts","an actor")))
        val c6id = dao.insert(CastDbModel(6,Cast("Actor6","acts","an actor")))
        val c7id = dao.insert(CastDbModel(7,Cast("Actor7","acts","an actor")))

        dao.insert(MovieCastCrossRef(m1id.toInt(),c1id.toInt()))
        dao.insert(MovieCastCrossRef(m1id.toInt(),c3id.toInt()))
        dao.insert(MovieCastCrossRef(m1id.toInt(),c5id.toInt()))
        dao.insert(MovieCastCrossRef(m1id.toInt(),c7id.toInt()))

        dao.insert(MovieCastCrossRef(m2id.toInt(),c2id.toInt()))
        dao.insert(MovieCastCrossRef(m2id.toInt(),c4id.toInt()))
        dao.insert(MovieCastCrossRef(m2id.toInt(),c6id.toInt()))

        dao.insert(MovieCastCrossRef(m3id.toInt(),c1id.toInt()))
        dao.insert(MovieCastCrossRef(m3id.toInt(),c2id.toInt()))
        dao.insert(MovieCastCrossRef(m3id.toInt(),c3id.toInt()))
        dao.insert(MovieCastCrossRef(m3id.toInt(),c4id.toInt()))
        dao.insert(MovieCastCrossRef(m3id.toInt(),c5id.toInt()))
        dao.insert(MovieCastCrossRef(m3id.toInt(),c6id.toInt()))
        dao.insert(MovieCastCrossRef(m3id.toInt(),c7id.toInt()))

        logCastWithMovie(c1id.toInt(),"C1")
        logMovieWithCast(m1id.toInt(),"M1")
        logCastWithMovie(c2id.toInt(),"C2")
        logMovieWithCast(m2id.toInt(),"M2")
        logCastWithMovie(c3id.toInt(),"C3")
        logMovieWithCast(m3id.toInt(),"M3")

    }

    fun logMovieWithCast(movieId: Int, tagSuffix: String) {
        var sb = StringBuilder()
        for (cawm in dao.getAllCastAssociatedWithMovie(movieId)) {
            for (c in cawm.castList) {
                sb.append("\n\t Name is ${c.cast.name} Profile is ${c.cast.profile_path} Character is ${c.cast.character}")
            }
            Log.d("DBINFO_CAWM_$tagSuffix","Movie is ${cawm.movie.title} cast are:$sb")
        }
    }
    fun logCastWithMovie(castId: Int, tagSuffix: String) {
        var sb = StringBuilder()
        for (mawc in dao.getAllMoviesAssociatedWithCast(castId)) {
            for (m in mawc.movieList) {
                sb.append("\n\tTitle is ${m.title} Overview is ${m.overview} Poster is ${m.poster_path}")
            }
            Log.d("DBINFO_MAWC_$tagSuffix","Cast is ${mawc.castList.cast.name} Movies are $sb")
        }
    }
}

Results in the log including:-

2022-09-03 08:15:25.084 D/DBINFO_MAWC_C1: Cast is Actor1 Movies are 
        Title is The Good, the Bad and the Ugly Overview is Western Poster is posetr001
        Title is Gone with the Wind Overview is Soppy Poster is poster003
        
2022-09-03 08:15:25.095 D/DBINFO_CAWM_M1: Movie is The Good, the Bad and the Ugly cast are:
         Name is Actor1 Profile is acts Character is an actor
         Name is Actor3 Profile is acts Character is an actor
         Name is Actor5 Profile is acts Character is an actor
         Name is Actor7 Profile is acts Character is an actor
         
2022-09-03 08:15:25.102 D/DBINFO_MAWC_C2: Cast is Actor2 Movies are 
        Title is Star Wars Overview is Sci-Fi Poster is poster002
        Title is Gone with the Wind Overview is Soppy Poster is poster003
        
2022-09-03 08:15:25.109 D/DBINFO_CAWM_M2: Movie is Star Wars cast are:
         Name is Actor2 Profile is acts Character is an actor
         Name is Actor4 Profile is acts Character is an actor
         Name is Actor6 Profile is acts Character is an actor
         
2022-09-03 08:15:25.111 D/DBINFO_MAWC_C3: Cast is Actor3 Movies are 
        Title is The Good, the Bad and the Ugly Overview is Western Poster is posetr001
        Title is Gone with the Wind Overview is Soppy Poster is poster003
        
2022-09-03 08:15:25.117 D/DBINFO_CAWM_M3: Movie is Gone with the Wind cast are:
         Name is Actor1 Profile is acts Character is an actor
         Name is Actor2 Profile is acts Character is an actor
         Name is Actor3 Profile is acts Character is an actor
         Name is Actor4 Profile is acts Character is an actor
         Name is Actor5 Profile is acts Character is an actor
         Name is Actor6 Profile is acts Character is an actor
         Name is Actor7 Profile is acts Character is an actor