Kotlin Exposed: Proper way to create a One To Many Relationship?

Question

I want to create an One-To-Many Relationship from the Order Entity to the OrderProductAmount Entity. I need this, because for each Order I need to know which Product's it contains and what the amount of each Product in the Order is, as one order can contain multiple products.

When I fetch Order Entities from the database, I want to be able to access all rows from OrderProductAmount that contain the corresponding orderId from it.

But whenever I access order.products from the result of findOrder I get null as the result.

I suppose there is some mistake in my setup of the entities or I am not inserting the entities into the database in the right way.

Order Entity

class Order(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Order>(Orders)

    var orderStatus by Orders.orderStatus
    val products by OrderProductAmount referrersOn OrderProductAmounts.order
}
object Orders : IntIdTable() {
    var orderStatus = enumeration("orderStatus", OrderStatus::class)
}

OrderProductAmount Entity

class OrderProductAmount(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<OrderProductAmount>(OrderProductAmounts)

    var order by Order referencedOn OrderProductAmounts.order
    var product by OrderProductAmounts.product
    var amount by OrderProductAmounts.amount
}

object OrderProductAmounts : IntIdTable() {
    var order = reference("order", Orders)
    var product = integer("product")
    var amount = integer("amount")
}

Product Entity

class Product(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Product>(Products)

    var name by Products.name
    var price by Products.price
}

object Products : IntIdTable() {
    val name = varchar("name", length = 256)
    val price = decimal("price", precision = 10, scale = 2)
}

Inserting new entities ...

 override suspend fun addOrder(productIdToAmount: Map<Int, Int>) = dbQuery {
        val orderId = Orders.insertAndGetId {
            it[Orders.orderStatus] = OrderStatus.CREATED
        }
        productIdToAmount.entries.forEach { (productId, amount) ->
            val product = productDAO.findProduct(productId)!!
            OrderProductAmounts.insert {
                it[OrderProductAmounts.order] = orderId.value
                it[OrderProductAmounts.product] = product.id.value
                it[OrderProductAmounts.amount] = amount
            }
        }
    }

Fetching an order from the database ...

override suspend fun findOrder(id: Int): Order? = dbQuery {
    Orders.select { Orders.id eq id }.map { Order.wrapRow(it) }.singleOrNull()
}

Answer 1

It seems that your mapping for products is a bit off.

It should be:

val products by Product via OrderProductAmounts

First comes the type of the collection, then the connection table.