如何返回具有3个表的multimap类型（Android Room数据库）

Question

我正在尝试在Android Room数据库的查询中进行三方连接。

我的表格关系如下：

1. Table1与Table2有一对一的关系 2. Table2与Table3有一对多的关系（Table3是多个）

我目前遇到的问题是dao方法的返回类型。

我希望使用较新的JOIN方法，返回一个multimap而不是创建定义关系的数据类（https://developer.android.com/training/data-storage/room/accessing-data#multimap）。

我不确定返回类型应该是什么。

我目前有Flow<Map<Table1, Pair<Table2, Table3>>>，它给我一个错误，说不确定如何将游标转换为此方法的返回类型。

“可能有一些我不知道的数据结构，可以让我返回三个表连接的数据？”

- SmallGrammer

Answer 1

一种不需要TypeConverter（用于处理Room无法处理的“Pair”）的方法是通过处理查询的笛卡尔积来生成Map的函数。

假设您有以下实体和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
)

您在一个带有@Dao注释的接口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
    }
}

• 请注意上述示例是为了演示在主线程上进行的，因此没有Flow<....>

然后在活动中执行以下操作（请注意，为了演示使用了.allowMainThreadQueries）：

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}")
        }
    }
}

结果如下：

结果是：

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

重要提示，尽管已经提取了预期的6行数据（第一部分），但仅有2个Map<Table1,Pair<Table2,Table3>>被提取出来。

这是因为Table1是MAP的键，因此它不能重复，相同的键会导致值被覆盖（可以看到table3的表名是最后处理的）。

- MikeT