How to use additional columns by Parent-Child reference?

[clemenskunert]

I have following two tables. I would like to specify an additional amount per entry for the join table PartToPart. My intention is to create a graph of the different combinations.

object Parts : IntIdTable() {
val partnumber = varchar("partnumber", 120).uniqueIndex()
val description = varchar("description", 255)
val internalDescription = varchar("internalDescription", 255).nullable()
val userId = reference("user", Users)
val parameter = text("parameter").nullable()
val partGroup = reference("partGroup", PartGroups)
}
object PartToParts : IntIdTable() {
val parent = reference("parent", Parts)
val child = reference("child", Parts)
val quantity = integer("quantity")
}

Here you can see my pitiful attempt. What is the right course of action?

class Part(id: EntityID) : IntEntity(id) {
companion object : IntEntityClass(Parts)
var partnumber by Parts.partnumber
var parents by Part.via(PartToParts.child, PartToParts.parent)
var children by Part.via(PartToParts.parent, PartToParts.child)
var quantity by PartToParts.quantity
var description by Parts.description
var internalDescription by Parts.internalDescription
var userId by User referencedOn Parts.userId
var parameter by Parts.parameter
var partGroup by PartGroup referencedOn Parts.partGroup
}

Thank you in advance for your suggestions ;)

[Tapac]

This is possible with dependsOnTables overrides on Part entity.
Please look here for example.

[clemenskunert]

I tried your approach but I had following Exception:

java.lang.reflect.InvocationTargetException
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
at kotlin.reflect.jvm.internal.calls.CallerImpl$Constructor.call(CallerImpl.kt:41)
at kotlin.reflect.jvm.internal.KCallableImpl.call(KCallableImpl.kt:106)
at org.jetbrains.exposed.dao.EntityClass.createInstance(Entity.kt:665)
at org.jetbrains.exposed.dao.EntityClass.wrap(Entity.kt:669)
at org.jetbrains.exposed.dao.EntityClass.wrapRow(Entity.kt:578)
at org.jetbrains.exposed.dao.EntityClass$wrapRows$1.invoke(Entity.kt:565)
at org.jetbrains.exposed.dao.EntityClass$wrapRows$1.invoke(Entity.kt:493)
at org.jetbrains.exposed.sql.IterableExKt$mapLazy$1$iterator$1.next(IterableEx.kt:128)
at de.renner.bde.backend.repository.Repository$allFromPartList$2.invoke(Repository.kt:246)
at de.renner.bde.backend.repository.Repository$allFromPartList$2.invoke(Repository.kt:12)
at de.renner.bde.backend.repository.DatabaseFactory$dbQuery$2$1.invoke(DatabaseFactory.kt:69)
at de.renner.bde.backend.repository.DatabaseFactory$dbQuery$2$1.invoke(DatabaseFactory.kt:9)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$1.invoke(ThreadLocalTransactionManager.kt:154)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$2.invoke(ThreadLocalTransactionManager.kt:195)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:202)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:194)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$transaction$1.invoke(ThreadLocalTransactionManager.kt:132)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:202)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:105)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:103)
at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:102)
at de.renner.bde.backend.repository.DatabaseFactory$dbQuery$2.invokeSuspend(DatabaseFactory.kt:67)
at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33)
at kotlinx.coroutines.DispatchedTask.run(Dispatched.kt:241)
at kotlinx.coroutines.scheduling.CoroutineScheduler.runSafely(CoroutineScheduler.kt:594)
at kotlinx.coroutines.scheduling.CoroutineScheduler.access$runSafely(CoroutineScheduler.kt:60)
at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.run(CoroutineScheduler.kt:742)
Caused by: java.lang.ExceptionInInitializerError
at de.renner.bde.backend.model.PartList.(objects.kt:153)
... 31 more
Caused by: java.lang.IllegalStateException: Cannot join with de.renner.bde.backend.model.Parts@4952ac0 as there is multiple primary key <-> foreign key references.
de.renner.bde.backend.model.Parts.id -> de.renner.bde.backend.model.PartLists.parent, de.renner.bde.backend.model.PartLists.child
at org.jetbrains.exposed.sql.Join.join(Table.kt:90)
at org.jetbrains.exposed.sql.Join.(Table.kt:62)
at org.jetbrains.exposed.sql.Join.(Table.kt:58)
at org.jetbrains.exposed.sql.Table.innerJoin(Table.kt:167)
at de.renner.bde.backend.model.Part.(objects.kt:131)
... 32 more

Here you can see my Part Entity:

class Part(id: EntityID) : IntEntity(id) {
companion object : IntEntityClass(Parts) {
override val dependsOnTables: ColumnSet = PartLists.innerJoin(Parts)
override fun createInstance(entityId: EntityID, row: ResultRow?): Part {
val tr = TransactionManager.current()
row?.getOrNull(Parts.id)?.let {
Part.wrap(it, row)
}
return super.createInstance(entityId, row)
}
}
var partNumber by Parts.partnumber
var parents by Part.via(PartLists.child, PartLists.parent)
var children by Part.via(PartLists.parent, PartLists.child)
var description by Parts.description
var internalDescription by Parts.internalDescription
var userId by User referencedOn Parts.userId
var parameter by Parts.parameter
var partGroup by PartGroup referencedOn Parts.partGroup
var quantity by PartLists.quantity
}
class PartList(id: EntityID) : IntEntity(id) {
companion object : IntEntityClass(PartLists)
var parent by Part referencedOn PartLists.parent
var child by Part referencedOn PartLists.child
var quantity by PartLists.quantity
}

[Tapac]

I don't understand what entity do you want to create.
Is there one-to-one or one-to-many relation? How Part could have multiple parents and children? What quantity means?

[clemenskunert]

Hi, thanks for your quick reply. I need to create a partlist for our products. It's for a operating instructions inside our production Data Acquisition app. So our employees need to know how often which part has to be installed.

[clemenskunert]

The Entity must have a parent to child relationship and for each the quantity of children there are used. My aim is to get tree of all our products from single screw until final product.

[karthikrao5]

Bump

[karthikrao5]

I have a similar problem. I have the parent-child table and am using the Table.via(Table.parent, Table.child) syntax and that works fine. But I also want to store data about that relationship in the same parent-child table. How can I reference that data piece from the parent or child entity?