CODEWITHSUNDEEP
androidkotlinandroid-room
amin mahmoudi
amin mahmoudi

Reputation: 660

how can get extra column in Junction table in many to many relationship in one query room android

I have factor table that have many item , that every item in factor have a quantity . below code show models and relationships.

how can Define relationship model to get quantity column in Junction table with two other table in one query and mapping them in models with room ?

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

item model:

@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 = "عدد"
)

Junction model :

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

Upvotes: 2

Views: 874

Answers (1)

user12734636
user12734636

Reputation:

This made simplier, you can do like

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

Dao's like are for using

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

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

for using tester did

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

gets to log is

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

more add to getting Factor with FactorItem list and Item list can use

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

like Dao code

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

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

Upvotes: 2

Related Questions