android - 如何在一个查询室android中的多对多关系中获得连接表中的额外列

Question

我有很多项目的因子表，因子中的每一项都有一个数量。
下面的代码显示了模型和关系。

如何定义关系模型以在一个查询中获得与其他两个表的连接表中的数量列，并在具有空间的模型中映射它们？

@Entity
data class Factor(
    @PrimaryKey
    @SerializedName("id")
    var FactorId: String = "",
    var description: String = "",
    var discount: Float = 0f,
    var receiver: Long = 0,
    var tax: Float = 0f,
    var transaction: Long,
    var status: Status = Status.PENDING,
    @Ignore
    var items: Array<Item>
) : DiffUtilProperty {
    override fun asSame(): Any? {
        return FactorId
    }
}

项目模型:

@Entity
data class Item(
    @PrimaryKey
    @SerializedName("id")
    var ItemId: String = "",
    var name: String = "",
    var discount: Float = 0f,
    var price: Float = 0f,
    @Ignore
    var quantity: Float = 0f,
    var unit: String = "عدد"
)

交界模型:

@Entity(
    indices = [
        Index("factor_id"),
        Index("item_id")
    ],
    foreignKeys = [
        ForeignKey(
            entity = Factor::class,
            parentColumns = ["FactorId"],
            childColumns = ["factor_id"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.RESTRICT
        ),
        ForeignKey(
            entity = Item::class,
            parentColumns = ["ItemId"],
            childColumns = ["item_id"],
            onDelete = ForeignKey.RESTRICT,
            onUpdate = ForeignKey.RESTRICT
        )]
)

data class FactorItem(
    @PrimaryKey val id: Long,
    val factor_id: String = "",
    val item_id: String = "",
    var quantity: Float = 0f
): DiffUtilProperty {
    override fun asSame(): Any? {
        return id
    }
}
    data class FactorWithItem(
    @Embedded var factor: Factor,
    @Relation(
        entityColumn = "item_id",
        parentColumn = "factor_id",
        associateBy = @Junction(FactorItem::class)
    ) val items: Array<Item>,
) : DiffUtilProperty {
    override fun asSame(): Any? {
        return factor.asSame()
    }
}

Answer 1

这样可以简化操作，您可以像

data class FactorWithItem(

    @Embedded
    var factorItem: FactorItem = FactorItem(),
    @Relation(
        entity = Factor::class,
        entityColumn = "FactorId",
        parentColumn = "factor_id"
    )
    var factor: Factor = Factor(),
    @Relation(
        entity = Item::class,
        entityColumn = "ItemId",
        parentColumn = "item_id"
    )
    var items: Item = Item()
)

道的喜欢用

@Transaction
@Query("SELECT * FROM FactorItem")
fun getAllFactorsWithItemsAndFactorItems(): List<FactorWithItem>

@Transaction
@Query("SELECT * FROM FactorItem WHERE factor_id = :factorId")
fun getFactorWithItemsByFactorId(factorId: String): List<FactorWithItem>

使用测试仪做了

    val i1 = Item("I1","Item 1",0f,10.50f,5f)
    val i2 = Item("I2","Item 2")
    val i3 = Item("I3","Item 3")
    factorItemDatabase.allDao().insertItem(i1)
    factorItemDatabase.allDao().insertItem(i2)
    factorItemDatabase.allDao().insertItem(i3)

    val i1 = Item("I1","Item 1",0f,10.50f,5f)
    val i2 = Item("I2","Item 2")
    val i3 = Item("I3","Item 3")
    factorItemDatabase.allDao().insertItem(i1)
    factorItemDatabase.allDao().insertItem(i2)
    factorItemDatabase.allDao().insertItem(i3)

    val f1 = Factor("F1","Factor 1")
    val f2 = Factor("F2","Factor 2")
    val f3 = Factor("F3","Factor 3")
    factorItemDatabase.allDao().insertFactor(f1)
    factorItemDatabase.allDao().insertFactor(f2)
    factorItemDatabase.allDao().insertFactor(f3)

    val fi_1_1 = FactorItem("F1","I1",15.67f)
    val fi_1_3 = FactorItem("F1","I3",254f)
    val fi_2_3 = FactorItem("F2","I3",33f)
    val fi_3_1 = FactorItem("F3","I1",109f)
    val fi_3_2 = FactorItem("F3","I2",133f)
    val fi_3_3 = FactorItem("F3","I3",-999.99f)
    factorItemDatabase.allDao().insertFactorItem(fi_1_1)
    factorItemDatabase.allDao().insertFactorItem(fi_1_3)
    factorItemDatabase.allDao().insertFactorItem(fi_2_3)
    factorItemDatabase.allDao().insertFactorItem(fi_3_1)
    factorItemDatabase.allDao().insertFactorItem(fi_3_2)
    factorItemDatabase.allDao().insertFactorItem(fi_3_3)

    var itemList = factorItemDatabase.allDao().getAllIetms()
    var factorList = factorItemDatabase.allDao().getAllFactors()
    var factorItemList = factorItemDatabase.allDao().getAllFactorItems()


    var fwiList = factorItemDatabase.allDao().getAllFactorsWithItemsAndFactorItems()
    for (fwi: FactorWithItem in fwiList) {
        Log.d("FWIALL","Factor is " + fwi.factor.description + " Item is " + fwi.items.name + " FWI QTY = " + fwi.factorItem.quantity)
    }
    fwiList = factorItemDatabase.allDao().getFactorWithItemsByFactorId("F3")
    for (fwi: FactorWithItem in fwiList) {
        Log.d("FWIBYFACTOR","Factor is " + fwi.factor.description + " Item is " + fwi.items.name + " FWI QTY = " + fwi.factorItem.quantity)
    }

获取日志是

D/FWIALL: Factor is Factor 1 Item is Item 1 FWI QTY = 15.67
D/FWIALL: Factor is Factor 1 Item is Item 3 FWI QTY = 254.0
D/FWIALL: Factor is Factor 2 Item is Item 3 FWI QTY = 33.0
D/FWIALL: Factor is Factor 3 Item is Item 1 FWI QTY = 109.0
D/FWIALL: Factor is Factor 3 Item is Item 2 FWI QTY = 133.0
D/FWIALL: Factor is Factor 3 Item is Item 3 FWI QTY = -999.99
D/FWIBYFACTOR: Factor is Factor 3 Item is Item 1 FWI QTY = 109.0
D/FWIBYFACTOR: Factor is Factor 3 Item is Item 2 FWI QTY = 133.0
D/FWIBYFACTOR: Factor is Factor 3 Item is Item 3 FWI QTY = -999.99

更多添加到带有FactorItem列表的Factor和Item列表可以使用

data class FactorWithFactorItemsWithItemsForFactor (

    @Embedded
    var factor: Factor = Factor(),
    @Relation(
        entity = FactorItem::class,
        entityColumn = "factor_id",
        parentColumn = "FactorId"
    )
    var factorItems: List<FactorItem> = emptyList(),
    @Relation(
        entity = Item::class,
        entityColumn = "ItemId",
        parentColumn = "FactorId",
        associateBy =(Junction(value = FactorItem::class,parentColumn = "factor_id",entityColumn = "item_id"))
    )
    var items: List<Item> = emptyList()
)

像道码

@Transaction
@Query("SELECT * FROM Factor")
fun getAllFactorsWithItemsWithFactorItem(): List<FactorWithFactorItemsWithItemsForFactor>

@Transaction
@Query("SELECT * FROM Factor WHERE FactorId = :factorId")
fun getFactorWithItemsWithFactorItemByFactorId(factorId: String): List<FactorWithFactorItemsWithItemsForFactor>