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

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?

1 answer

  • answered 2022-05-07 02:34 MikeT

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

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum