CODEWITHSUNDEEP
kotlindaokotlin-exposed
mars8
mars8

Reputation: 1246

Return a nested list of child table using Kotlin Exposed Dao

I have a parent table of stores and items it sells. I then have a child table with units sold for each item across the stores. I would like to have an exposed DAO entity which gives me the item and store details from the parent table and the units sold for each item as a nested list from child table.

I understand that referrersOn and referencedOn can be used, however in the documentations StarWars example, the child table needs to have a column indicating the parent_id row it is joined to. This does not work in my case as my child table can be associated to multiple parent_ids.

How can I update the below code so the parent entity class returns me the nested list of units sold, using the item column as the link between the two tables?

Parent Table

parent_id Store Name Region Item
1 Store1 Texas ItemA
2 Store2 Nevada ItemB
3 Store3 Florida ItemA
4 Store4 Nevada ItemB
5 Store5 Arizona ItemA 
// Table A definition
object TableA : IntIdTable() {
    val storeName = varchar("store_name", 50).uniqueIndex()
    val region = varchar("region", 50)
    val item = varchar("item", 50)
}

// Entity class for Table A
class TableAEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<TableAEntity>(TableA)

    var storeName by TableA.storeName
    var region by TableA.region
    var item by TableA.item
}

Child Table

child_id Item Date Units sold
1 ItemA 2023-05-20 10
2 ItemA 2023-05-21 15
3 ItemA 2023-05-22 8
4 ItemB 2023-05-20 5
5 ItemB 2023-05-21 12
6 ItemB 2023-05-22 6 
// Table B definition
object TableB : IntIdTable() {
    val item = reference("item", TableA)
    val date = date("date")
    val unitsSold = integer("units_sold")
}

// Entity class for Table B
class TableBEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<TableBEntity>(TableB)

    var item by TableB.item
    var date by TableB.date
    var unitsSold by TableB.unitsSold
}

Upvotes: 2

Views: 433

Answers (1)

Jens Witteveen
Jens Witteveen

Reputation: 1

It sounds like you need a many-to-many reference.

Create an intermediate table for storing references:

object TableATableB : Table() {
    val tableA = reference("tableA", TableA)
    val tableB = reference("tableB", TableB)
    override val primaryKey = PrimaryKey(tableA, tableB)
}

Add a reference to TableA

// Table A definition
// Entity class for Table A
class TableAEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<TableAEntity>(TableA)

    var storeName by TableA.storeName
    var region by TableA.region
    var item by TableA.item
    var tableB by TableBEntity via TableATableB
}

(I would use tablenames that reflect the contents)

See https://github.com/JetBrains/Exposed/wiki/DAO#many-to-many-reference

Upvotes: -1

Related Questions