Any example of reflexive many to many relation in room

Question

Following android developers guide

Lets say, a task can be made by many other tasks (0,n) and a task can make many other tasks (0,n)

So we got our entity with it's id and a name atribute

@Entity(tableName = "tasks")
data class Task(
    @PrimaryKey(autoGenerate = true) val taskId: Long = 0,
    val name: String
)

Our cross reference

@Entity(primaryKeys = ["parentTaskId", "childTaskId"])
data class TaskTaskCR(
    val parentTaskId: Long,
    val childTaskId: Long
)

Our data class for later queries

data class TaskWithTasks(
    @Embedded val task: Task,
    @Relation(
        parentColumn = "taskId",
        entityColumn = "childTaskId",
        associateBy = Junction(TaskTaskCR::class)
    )
    val tasks: List<Task>
)

And our DAO

@Dao
    interface TaskWithTasksDAO {
        @Transaction
        @Query("SELECT * FROM tasks")
        fun getTasksWithTasks(): List<TaskWithTasks>
    }

The data class seems to be wrong so the dao too

How would be a correct taskwithtasks data class?

Answer 1

The data class seems to be wrong so the dao too

There is an issue with the TaskWithTasks.

The TaskWithTasksDAO is fine (see demo).

The Issue

• Rhetorically, which column in the cross reference table does the parent/child come from?

  • additionally, the entityColumn in the @Relation, as opposed to the Junction needs to point to the column in the child table (i.e. the Task table), NOT to the column in the cross reference table. see the comments in the suggested code for this additional fix

You need to tell Room this by using the parentColumn and entityColumn parameters of the Junction specifying the respective columns to be used.

How would be a correct taskwithtasks data class?

I believe that you should be using:-

data class TaskWithTasks(
    @Embedded val task: Task,
    @Relation(
        parentColumn = "taskId", /* column in the parent Task */
        entityColumn = "taskId", /* <<<<<CHANGED>>>>> column in the xreferenced child Task */
        associateBy = Junction(
            TaskTaskCR::class,
            parentColumn = "parentTaskId", /* <<<<<ADDED>>>>> column in the cross reference table that references the parent Task */
            entityColumn = "childTaskId" /* <<<<<ADDED>>>>> column in the cross reference table that references the child Task */
        )
    )
    val tasks: List<Task>
)

• comments added to explain

Demo

Using:-

@Entity(tableName = "tasks")
data class Task(
    @PrimaryKey(autoGenerate = true) val taskId: Long = 0,
    val name: String
)
/* Our cross reference */

@Entity(primaryKeys = ["parentTaskId", "childTaskId"])
data class TaskTaskCR(
    val parentTaskId: Long,
    val childTaskId: Long
)
/*Our data class for later queries*/

data class TaskWithTasks(
    @Embedded val task: Task,
    @Relation(
        parentColumn = "taskId", /* column in the parent Task */
        entityColumn = "taskId", /* column in the xreferenced child Task */
        associateBy = Junction(
            TaskTaskCR::class,
            parentColumn = "parentTaskId", /* column in the cross reference table that references the parent Task */
            entityColumn = "childTaskId" /* column in the cross reference table that references the child Task */
        )
    )
    val tasks: List<Task>
)
/*And our DAO*/

@Dao
interface TaskWithTasksDAO {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(task: Task): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(taskCR: TaskTaskCR): Long
    @Transaction
    @Query("SELECT * FROM tasks")
    fun getTasksWithTasks(): List<TaskWithTasks>
}

@Database(entities = [Task::class,TaskTaskCR::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getTaskWithTaskDao(): TaskWithTasksDAO
    companion object {
        private var instance: TheDatabase?=null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance=Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

• Note for brevity the main thread has been used for the demo

AND :-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: TaskWithTasksDAO
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getTaskWithTaskDao()
        val t1id = dao.insert(Task(name = "T1"))
        val t2id = dao.insert(Task(name = "T2"))
        val t3id = dao.insert(Task(name = "T3"))
        val t4id = dao.insert(Task(name = "T4"))

        dao.insert(TaskTaskCR(t1id,t1id))
        dao.insert(TaskTaskCR(t1id,t2id))
        dao.insert(TaskTaskCR(t1id,t3id))
        dao.insert(TaskTaskCR(t2id,t4id))
        dao.insert(TaskTaskCR(t3id,t1id))
        dao.insert(TaskTaskCR(t3id,t3id))

        for (ttcr in dao.getTasksWithTasks()) {
            val sb = StringBuilder()
            for (childt in ttcr.tasks) {
                sb.append("\n\t Child Task is ${childt.name} ID is ${childt.taskId}")
            }
            Log.d("DBINFO","Parent Task is ${ttcr.task.name} ID is ${ttcr.task.taskId}. It has ${ttcr.tasks.size} children they are:-${sb}")
        }
    }
}

When run, results in the log including:-

2024-04-17 15:30:24.451 D/DBINFO: Parent Task is T1 ID is 1. It has 3 children they are:-
         Child Task is T1 ID is 1
         Child Task is T2 ID is 2
         Child Task is T3 ID is 3
2024-04-17 15:30:24.451 D/DBINFO: Parent Task is T2 ID is 2. It has 1 children they are:-
         Child Task is T4 ID is 4
2024-04-17 15:30:24.451 D/DBINFO: Parent Task is T3 ID is 3. It has 2 children they are:-
         Child Task is T1 ID is 1
         Child Task is T3 ID is 3
2024-04-17 15:30:24.451 D/DBINFO: Parent Task is T4 ID is 4. It has 0 children they are:-

• i.e. as expected