I've been reading through the <https://github.com/...

# exposed

[Chris Werner Rau]

I've been reading through the https://github.com/JetBrains/Exposed/wiki/DAO#many-to-one-reference but I can't figure it out

[Daniel Weidensdörfer]

Can you please state your problem?

[Chris Werner Rau]

I have 2 entities;

class KubernetesCluster(
    id: EntityID<String>,
) : Entity<String>(id) {
    companion object : EntityClass<String, KubernetesCluster>(KubernetesClusterTable)

    val name by KubernetesClusterTable.name
1. //    val slas by ServiceLevelAgreement referrersOn ServiceLevelAgreementTable.cluster // doesn't work because I also don't have a third table?
2. //    val slas by ServiceLevelAgreement via ServiceLevelAgreementTable // doesn't work as I don't have a third table
}

object KubernetesClusterTable : IdTable<String>("kubernetes_cluster") {
    override val id = text("id").entityId()
    val name = text("name")
}

class ServiceLevelAgreement(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<ServiceLevelAgreement>(ServiceLevelAgreementTable)

    val since by ServiceLevelAgreementTable.since
}

object ServiceLevelAgreementTable : IntIdTable("kubernetes_cluster_sla") {
    val cluster = reference("cluster_id", KubernetesClusterTable)
    val since = date("start_date")
}

And I'm trying to list all SLAs for the KubernetesCluster

Now, when I use the first option, the referrersOn, I get the following error;

ERROR: operator does not exist: integer = character varying

Which happens because exposed is trying to find the SLA by its ID but it's using the KubernetesCluster.id for this, which of course won't work.

When I try the second option, the via, I get the following error;

Table does not reference target

Which looks like it's happening because it's trying to find the column in the SLATable which references the SLATable?, see https://github.com/JetBrains/Exposed/blob/main/exposed-dao/src/main/kotlin/org/jetbrains/exposed/dao/InnerTableLink.kt#L42-L44

I've looked through https://github.com/JetBrains/Exposed/wiki/DAO#many-to-one-reference but I can't for the life of me figure out how to do this.

[Daniel Weidensdörfer]

Maybe you can format the code correctly?

[Chris Werner Rau]

Uff, is this not markdown?

[Daniel Weidensdörfer]

Well, just use the slack text editor buttons 🙂

[Daniel Weidensdörfer]

awesome

[Daniel Weidensdörfer]

First thing I see is that the SLA Entity is missing something like

var film by StarWarsFilm referencedOn UserRatings.film // use referencedOn for normal references

(copied from the docs)

[Chris Werner Rau]

Is that necessary?
I don't need to access the cluster from the SLA object 🤔

[Daniel Weidensdörfer]

Not sure if its necessary, when do those errors appear?

[Daniel Weidensdörfer]

To 1. You dont need a 3rd table for referencedOn

[Chris Werner Rau]

They appear when I try to load all clusters;

KubernetesCluster
            .all()
            .with(
                KubernetesCluster::slas,
            )
            .toList()

[Daniel Weidensdörfer]

Okay, I'm currently just using H2 db, should not matter though.
Here is a little minimum example that works for me:

object Children : IntIdTable() {
    val parent = reference("parent", Parents)
}

class Child(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Child>(Children)
    
    var parent by Parent referencedOn Children.parent
    
}

object Parents : IntIdTable() {
    val name = varchar("name", 256)
}

class Parent(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Parent>(Parents)
    
    var name by Parents.name
    val children by Child referrersOn Children.parent
    
}

[Chris Werner Rau]

Mh, that does not work for me and results in the same error

[Chris Werner Rau]

During warmUpReferences the dao goes through https://github.com/JetBrains/Exposed/blob/main/exposed-dao/src/main/kotlin/org/jetbrains/exposed/dao/EntityClass.kt#L383-L389 in the context of the ServiceLevelAgreement$Companion.
The distinctRefIds are the cluster IDs and it tries to findById the ServiceLevelAgreement by the cluster id which of course doesn't work and results in the wrong type PG exception

[Chris Werner Rau]

I created a reproduction zip, just start a postgres (podman run --rm -it -e POSTGRES_PASSWORD=test -e POSTGRES_USER=test -e POSTGRES_DB=domain --network host postgres) and run gradle run inside the directory

join-error.zip

[Daniel Weidensdörfer]

Does it work with h2? I just followed the ktor doc on doing that. https://ktor.io/docs/interactive-website-add-persistence.html

[Chris Werner Rau]

I wouldn't think so, as the logical path would be the same? And getting the SLA by the it's ID but with the cluster_id can never work, whatever database I'm using
But I'm going to try 👍️

[Chris Werner Rau]

Oh, yeah, it doesn't work out of the box as I'm using postgres stuff 😅
I'm going to "rewrite" it

But did my reproduction zip fail for you too?

[Chris Werner Rau]

I get errors when trying to create the tables in h2;

org.h2.jdbc.JdbcSQLSyntaxErrorException: Constraint "PRIMARY KEY | UNIQUE (NAME)" not found; SQL statement:
CREATE TABLE IF NOT EXISTS KUBERNETES_CLUSTER (ID TEXT NOT NULL, CUSTOMER_ID INT NOT NULL, "NAME" TEXT NOT NULL, CLOUD TEXT NOT NULL, CONSTRAINT FK_KUBERNETES_CLUSTER_CUSTOMER_ID__ID FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(ID) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT FK_KUBERNETES_CLUSTER_CLOUD__NAME FOREIGN KEY (CLOUD) REFERENCES CLOUD("NAME") ON DELETE RESTRICT ON UPDATE RESTRICT)

But I'm not using h2 anyways, so 😅

[Daniel Weidensdörfer]

But did my reproduction zip fail for you too?

I didnt try, I'm not at the postgres/kubernetes stage yet 😄

[Chris Werner Rau]

You don't need kubernetes for this, you can just start a local postgres container 😅

[Chris Werner Rau]

If you have docker instead of podman you can just replace podman with docker