How to return multimap type with 3 tables (Android Room Databases)

Question

I am trying to do a 3 way JOIN in my query for my Android Room Database.

My tables relationships are:

1. Table1 has a 1 to 1 relationship with Table2
2. Table2 has a 1 to many relationship with Table3 (Table3 is the many)

I'm currently having issues with my return type on my dao method.

I wish to use the newer method doing JOINs, which is returning a multimap instead of creating a data class defining the relationship (https://developer.android.com/training/data-storage/room/accessing-data#multimap).

I'm not sure what the return type should be.

I currently have Flow<Map<Table1, Pair<Table2, Table3>>> which gives me an error saying Not sure how to convert a Cursor to thsi method's return type.

There's probably some data structure I don't know about that can let me return the data of a 3 way JOIN?

Answer 1

One way, not requiring a TypeConverter (to handle the 'Pair' which room doesn't know how to handle) would be to generate the Map via a function that handle the cartesian product of the query.

Say you had the following Entities and a POJO :-

@Entity
data class Table1(
    @PrimaryKey
    var table1Id: Long?=null,
    var table1Name: String
)
@Entity(
    foreignKeys = [
        ForeignKey(Table1::class,["table1Id"],["table2Id"],ForeignKey.CASCADE, ForeignKey.CASCADE,false)
    ]
)
data class Table2(
    @PrimaryKey
    var table2Id: Long,
    var table2Name: String
)
@Entity(
    foreignKeys = [
        ForeignKey(Table2::class,["table2Id"],["table2IdRef"],ForeignKey.CASCADE,ForeignKey.CASCADE, false)
    ]
)
data class Table3(
    @PrimaryKey
    var table3Id: Long?=null,
    @ColumnInfo(index = true)
    var table2IdRef: Long,
    var table3Name: String
)

data class Table1WithTable2WithTable3s(
    @Embedded
    var table1: Table1,
    @Embedded
    var table2: Table2,
    @Embedded
    var table3: Table3
)

And you had The following in an @Dao annotated interface AllDao :-

@Dao
interface AllDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(table1: Table1): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(table2: Table2): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(table3: Table3): Long

    @Query("SELECT * FROM table1")
    fun getAllFromTable1(): List<Table1>


    @Query("SELECT * FROM table1 JOIN table2 ON table1.table1Id = table2.table2Id JOiN table3 ON table2.table2Id = table3.table2IdRef ORDER BY table1.table1Id")
    fun getTable1WithTable2WithTable3Rows(): List<Table1WithTable2WithTable3s>

    fun getMappings(): Map<Table1,Pair<Table2,Table3>> {
        val rv: MutableMap<Table1,Pair<Table2,Table3>> = mutableMapOf()

        /* This will utilise the getTable1WithTable2WithTable3Rows */
        /* and convert to the Map<Table1,Pair<Table2,Table3>> */
        /* Logging each row extracted from the database */
        for(t1wt2wt3 in getTable1WithTable2WithTable3Rows()) {
            Log.d(
                "DBINFO",
                "Extracted Row is " +
                        "T1NAME = ${t1wt2wt3.table1.table1Name} " +
                        "T2NAME is ${t1wt2wt3.table2.table2Name} " +
                        "T3NAME is ${t1wt2wt3.table3.table3Name}")
            rv[t1wt2wt3.table1] = Pair(t1wt2wt3.table2,t1wt2wt3.table3)
        }
        return rv
    }
}

• Note the above is designed to demonstrate on the main thread so no Flow<....>

Then the following in an activity (note that .allowMainThreadQueries has been used for the demo):-

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

        dao.insert(Table1(1,table1Name = "T1_1"))
        dao.insert(Table1(2,table1Name = "T1_2"))
        dao.insert(Table2(table2Id = 1,"T2_1"))
        dao.insert(Table2(table2Id = 2, table2Name = "T2_2"))
        dao.insert(Table3(table2IdRef = 1, table3Name = "T3_1"))
        dao.insert(Table3(table2IdRef = 1, table3Name = "T3_2"))
        dao.insert(Table3(table2IdRef = 1, table3Name = "T3_3"))
        dao.insert(Table3(table2IdRef = 2, table3Name = "T3_4"))
        dao.insert(Table3(table2IdRef = 2, table3Name = "T3_5"))
        dao.insert(Table3(table2IdRef = 2, table3Name = "T3_6"))

        for (m in dao.getMappings()) {
            Log.d("DBINFO",
                "T1ID = ${m.key.table1Id} " +
                        "T1NAME = ${m.key.table1Name} " +
                        "T2NAME =${m.value.first.table2Name} " +
                        "T3NAME  ${m.value.second.table3Name}")
        }
    }
}

The the result is :-

2022-05-07 12:18:03.820 D/DBINFO: Extracted Row is T1NAME = T1_1 T2NAME is T2_1 T3NAME is T3_1
2022-05-07 12:18:03.820 D/DBINFO: Extracted Row is T1NAME = T1_1 T2NAME is T2_1 T3NAME is T3_2
2022-05-07 12:18:03.820 D/DBINFO: Extracted Row is T1NAME = T1_1 T2NAME is T2_1 T3NAME is T3_3
2022-05-07 12:18:03.821 D/DBINFO: Extracted Row is T1NAME = T1_2 T2NAME is T2_2 T3NAME is T3_4
2022-05-07 12:18:03.821 D/DBINFO: Extracted Row is T1NAME = T1_2 T2NAME is T2_2 T3NAME is T3_5
2022-05-07 12:18:03.821 D/DBINFO: Extracted Row is T1NAME = T1_2 T2NAME is T2_2 T3NAME is T3_6


2022-05-07 12:18:03.821 D/DBINFO: T1ID = 1 T1NAME = T1_1 T2NAME =T2_1 T3NAME  T3_3
2022-05-07 12:18:03.821 D/DBINFO: T1ID = 2 T1NAME = T1_2 T2NAME =T2_2 T3NAME  T3_6

IMPORTANT NOTE However, as can be seen even though the 6 expected rows have been extracted (the first section) only 2 Map<Table1,Pair<Table2,Table3>> have resulted.

This is because Table1 is the key to the MAP and as such it cannot be repeated, rather the same key results in the value being overwritten (as can be seen as the tablename for table3 is the last handled).