时间戳

首页 > 解决方案 > 如何在一个查询室android中以多对多关系在Junction表中获得额外的列

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

问题描述

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

如何定义关系模型以在一个查询中获取 Junction 表中的数量列与另外两个表并将它们映射到具有房间的模型中？

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

标签： androidkotlinandroid-room

解决方案

这变得更简单了，你可以这样做

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 列表和 Item 列表获取 Factor 可以使用

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>

推荐阅读