how to populate intermediate table in Android

Question

I have two tables with a many-to-many relationship. So I created an intermediate table, but I can't find a way to populate this table correctly because I can't set a correct list from my data.

I have a list of 'courses' : each 'course' can have one or several categories. So my table looks like this :

|idcourses|title|date|categories|
|----|----|----|----|
|700|title1|01012021|[54]|
|701|title2|01022021|[54]|
|702|title3|01032021|[48]|
|868|title4|01042021|[47, 52, 54]|

If I try a map like this :

val myMap = coursesList.map { itcategory to it.idcourses}.distinct()

I have this kind of result :

([54], 700), ([54], 701), ([48], 702), ([47, 52, 54], 868)

The whole "[47, 52, 54]" is considered as one string but I want it to be split so I can have this :

([54], 700), ([54], 701), ([48], 702), ([47], 868), ([52], 868), ([54], 868)

Does anyone know how to achieve this ??

Answer 1

I believe that you may be trying to do this the wrong way as it appears that your intermediate table has a column where you are expecting a list of category id's.

You cannot have a column that is a list/array it has to be a single object.

However rather than try to fix that, what would typically be used for an intermediate table is a table that primarily has a single row per mapping. That is two columns that make up a mapping. Where the two columns are a composite primary key.

• other columns that have data specific to the mapping can be used.

• In your case one column to map/reference/relate/associate to the course and an second column to map the course.

For example, say you have the Course Table and the Category Table per:-

@Entity
data class Course(
    @PrimaryKey
    val idcourses: Long? = null,
    val title: String,
    val date: String
)

and

@Entity
data class Category(
    @PrimaryKey
    val idcategories: Long? = null,
    val name: String
)

Then you could have the intermediate table as :-

@Entity(primaryKeys = ["idcoursesmap","idcategoriesmap"])
data class CourseCategoryMap(
    val idcoursesmap: Long,
    @ColumnInfo(index = true)
    val idcategoriesmap: Long
)

• the index on the idcategoriesmap will likely improve the efficiency. Room would also issue a warning.
• you may wish to consider defining Foreign Key constraints to enforce referential integrity. None have been included for brevity.

This is sufficient for a many-many relationship.

You would probably want to retrieve Courses with the Categories so you would probably want a POJO for this such as:-

data class CourseWithCategories(
    @Embedded
    val course: Course,
    @Relation(
        entity = Category::class,
        parentColumn = "idcourses",
        entityColumn = "idcategories",
        associateBy = Junction(
            value = CourseCategoryMap::class,
            parentColumn = "idcoursesmap",
            entityColumn = "idcategoriesmap"
        )
    )
    val categories: List<Category>
)

Here's some Dao's that would or may be wanted/useful:-

abstract class AllDao {
    @Insert(onConflict = IGNORE) // Insert single Category
    abstract fun insert(category: Category): Long
    @Insert(onConflict = IGNORE) // Insert Single Course
    abstract fun insert(course: Course): Long
    @Insert(onConflict = IGNORE) // Insert Single CourseCategoryMap
    abstract fun insert(courseCategoryMap: CourseCategoryMap): Long
    /* Inserts many course category maps */
    @Insert(onConflict = IGNORE)
    abstract fun insert(courseCategoryMaps: List<CourseCategoryMap>): List<Long>

    @Query("SELECT * FROM course WHERE course.title=:courseTitle")
    abstract fun getCourseByTitle(courseTitle: String): Course
    @Query("SELECT * FROM category WHERE category.name LIKE :categoryMask")
    abstract fun getCategoriesByNameMask(categoryMask: String): List<Category>
    /* For retrieving courses with all the courses categories */
    @Transaction
    @Query("SELECT * FROM course")
    abstract fun getAllCoursesWithCategories(): List<CourseWithCategories>

    @Transaction
    @Query("")
    fun insertManyCataegoriesForACourseByIds(idcourse: Long,categories: List<Long>) {
        for (categoryId: Long in categories) {
            insert(CourseCategoryMap(idcourse,categoryId))
        }
    }

    // Anoher possibility
    @Transaction
    @Query("")
    fun insertManyCategoriesForACourse(course: Course, categories: List<Category>) {
        val categoryIds = ArrayList<Long>()
        for (c: Category in categories) {
            categoryIds.add(c.idcategories!!)
        }
        insertManyCataegoriesForACourseByIds(course.idcourses!!,categoryIds)
    }
}

Demonstration

To demonstrate the above, a pretty standard class annotated with @Database :-

const val DATABASE_NAME = "the_database.db"
const val DATABASE_VERSION =1
@Database(entities = [Course::class,Category::class,CourseCategoryMap::class], exportSchema = false, version = DATABASE_VERSION)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    companion object {
        @Volatile
        private var instance: TheDatabase? = null
        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java, DATABASE_NAME)
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

And activity code to replicate what it looks like your are attempting (but twice to show 2 ways of mapping, the second using category id's that are 20 greater then the first) :-

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()

    dao.insert(Course(idcourses = 700,title = "title1", date = "01012021"))
    dao.insert(Course(701,"title2","01022021"))
    dao.insert(Course(702,"title3","01032021"))
    dao.insert(Course(868,"title4","01042021"))

    // add quite a few categories for demo
    for(i in 30..300) {
        dao.insert(Category(i.toLong(),"Category${i}"))
    }

    //example of what you are trying to do (first)
    var currentCourse = dao.getCourseByTitle("title1")
    dao.insertManyCataegoriesForACourseByIds(currentCourse.idcourses!!, listOf(54))
    dao.insertManyCataegoriesForACourseByIds(dao.getCourseByTitle("title2").idcourses!!, listOf(54))
    dao.insertManyCataegoriesForACourseByIds(dao.getCourseByTitle("title3").idcourses!!, listOf(48))
    dao.insertManyCataegoriesForACourseByIds(dao.getCourseByTitle("title4").idcourses!!, listOf(47,52,54))

    // second (does the same but uses categroyids 20 greater than the first)
    val coursecategorymaplist = listOf<CourseCategoryMap>(
        CourseCategoryMap(700,74),
        CourseCategoryMap(701,74),
        CourseCategoryMap(702,68),
        CourseCategoryMap(868,67),
        CourseCategoryMap(868,72),
        CourseCategoryMap(868,74)
    )
    dao.insert(coursecategorymaplist)

    // Extract results
    for (cwc: CourseWithCategories in dao.getAllCoursesWithCategories()) {
        Log.d("DBINFO","Course is ${cwc.course.title}, date is ${cwc.course.date} it has ${cwc.categories.size} categories they are:-")
        for (c: Category in cwc.categories) {
            Log.d("DBINFO","\tCategory is ${c.name}")
        }
    }
}

Results

The log includes (note double the number of categories):-

D/DBINFO: Course is title1, date is 01012021 it has 2 categories they are:-
D/DBINFO:   Category is Category54
D/DBINFO:   Category is Category74
D/DBINFO: Course is title2, date is 01022021 it has 2 categories they are:-
D/DBINFO:   Category is Category54
D/DBINFO:   Category is Category74
D/DBINFO: Course is title3, date is 01032021 it has 2 categories they are:-
D/DBINFO:   Category is Category48
D/DBINFO:   Category is Category68
D/DBINFO: Course is title4, date is 01042021 it has 6 categories they are:-
D/DBINFO:   Category is Category47
D/DBINFO:   Category is Category52
D/DBINFO:   Category is Category54
D/DBINFO:   Category is Category67
D/DBINFO:   Category is Category72
D/DBINFO:   Category is Category74

The Database

The Course Table :-

The Category Table (partial)

The CourseCategoryMap (intermediate table)