Exposed: DAO on compound/multiple primary key ?

[scorsi]

Hello,

PostgreSQL can use multiple fields as Primary Key (doc) but how to handle that with Exposed DAO ?

I tried to use the DAO with a table which is quite complexe. Because example is better than words. Here the SQL :

CREATE TABLE table__contacts_first
(
    contact_id    UUID         NOT NULL,
    app_id        VARCHAR(128) NOT NULL,

    --- other fields ...

    FOREIGN KEY (contact_id) REFERENCES table__contacts (contact_id),
    PRIMARY KEY (contact_id, app_id)
);

As you can see, the table__contacts_first doesn't have any PK, but the pair contact_id + app_id acts as is.
I have the following Exposed Table:

object ContactsFirstTable : Table(name = "table__contacts_first") {
    val contactId = reference("contact_id", ContactsTable).primaryKey()
    val appId = varchar("app_id", length = 128).primaryKey()
}

And now the Exposed DAO:

class ContactFirstDao(id: EntityID<UUID>) : UUIDEntity(id) {
    companion object : UUIDEntityClass<ContactFirstDao>(ContactsFirstTable)
}

Obviously, I got errors and in fact, I should construct my ContactFirstDao using contactId and appId.

Do you have any idea how I could achieve that ?

Thanks 👍

[michaelbrylevskii]

Hi, maybe I didn’t understand it, but according to this guide we can specify two primary keys in the table definition and bind table fields to entity fields.
Section "8.8. Many-to-Many Associations" https://www.baeldung.com/kotlin-exposed-persistence

[scorsi]

Hello @michaelbrylevskii thanks for the answer but it didn't answer to my question at all.

It is not a "many-to-many" associations but just a foreign key on a compound primary keys.

I have a table with two primary key, how could I create my Dao:

class MyDao(primaryKeyOne: EntityID<UUID>, primaryKeyTwo: EntityID<UUID>) : UUIDEntity(primaryKeyOne, primaryKeyTwo) {
    companion object : UUIDEntityClass<MyDao>(MyTable)
}
class MyDao(primaryKey EntityID<UUID, UUID>) : UUIDEntity(primaryKey) {
    companion object : UUIDEntityClass<MyDao>(MyTable)
}
class MyDao(primaryKey EntityID<Pair<UUID, UUID>>) : UUIDEntity(primaryKey) {
    companion object : UUIDEntityClass<MyDao>(MyTable)
}

All of thoses don't work. It is missing in the library.

Do you understand now ?

[JulianBissekkou]

Is there any update to this? I am looking for this feature as well

[scorsi]

I left Exposed caused of that important issue. Anyone in the staff behind the lib seams to be interested into this, it's still unassigned...

[PedroD]

This is something so basic at the SQL level, why is Exposed failing at addressing this? So strange.

[scorsi]

Agreed, very strange.

[jnfeinstein]

I naively attempted to use a CompositeColumn as the ID column, but was blocked by this. Composite columns appear to inherit from Expression and not the requisite Column interface. So close!

data class BiCompositeId<T : Comparable<T>, U: Comparable<U>>(
    val first: T,
    val second: U
) : Comparable<BiCompositeId<T, U>> {
    override fun compareTo(other: BiCompositeId<T, U>): Int {
        return first.compareTo(other.first).let {
            if (it != 0) it else second.compareTo(other.second)
        }
    }
}

@Suppress("unchecked_cast")
class BiCompositeIdColumn<T : Comparable<T>, U: Comparable<U>>(
    val first: Column<T>,
    val second: Column<U>
) : BiCompositeColumn<T, U, BiCompositeId<T, U>?>(
    column1 = first,
    column2 = second,
    transformFromValue = { id -> id?.first to id?.second },
    transformToValue = { firstValue, secondValue ->
        if (firstValue != null && secondValue != null) {
            BiCompositeId(firstValue as T, secondValue as U)
        } else {
            null
        }
    }
)

abstract class BiCompositeIdTable<T : Comparable<T>, U: Comparable<U>>(name: String = "") : IdTable<BiCompositeId<T, U>>(name) {
    abstract val first: Column<T>
    abstract val second: Column<U>
    override val id by lazy { biCompositeIdColumn(first, second).entityId() } // sad-face :-(
    override val primaryKey by lazy { super.primaryKey ?: PrimaryKey(first, second) }
}

[scorsi]

An amaizing library: SQL ! I stopped using libraries, ORMs, ... Just SQL is enough.
But you can take a look at Ktorm (https://ktorm.liuwj.me/).

I left that issue open because it's an importante missing feature.

[PedroD]

An amaizing library: SQL ! I stopped using libraries, ORMs, ... Just SQL is enough.
But you can take a look at Ktorm (https://ktorm.liuwj.me/).

I left that issue open because it's an importante missing feature.

That's been my approach since ever, but I got tired of making all CRUD methods manually for every new project. Waste of time, prone to error, and more code to maintain.

[PedroD]

I left that issue open because it's an importante missing feature.

If Ktorm can do this, then I'm sold: https://github.com/kotlin-orm/ktorm/issues/220

[sickfar]

Hello there

Just created an extension library for that (modifying exposed-dao part). Hope it will help you
https://gitlab.com/sickfar/exposed-dao-composite