Getting autogenerated id after insert - ktor exposed

user_6275174 0
Viewed 521

I'm new to ktor and I'm trying to insert a row in a mysql table with Ktor Exposed. The table is defined as below:

object Posts : Table("wpv1_posts") {
    val id: Column<Int> = integer("ID").uniqueIndex()
    val author: Column<Int> = integer("post_author")
    val title = varchar("post_title", 50)
    val date: Column<DateTime> = datetime("post_date")
    val dateGMT: Column<DateTime> = datetime("post_date_gmt")
    val content = text("post_content")
    val excerpt = text("post_excerpt")
    val guid = text("guid")
    val postType = text("post_type")
    val toPing = text("to_ping")
    val pinged = text("pinged")
    val postContentFiltered = text("post_content_filtered")
    val postMimeType = text("post_mime_type")
    val postStatus = text("post_status")
    val postParent = integer("post_parent")
}

I'm able to insert the row with an insert statement but I can't retrieve the inserted value, I get an error:

com.babacomarket.backend.model.database.Posts.ID is not in record set

override fun createSubscription(subscription: SubscriptionRequest): Int {
        return transaction {
            val currentDate = DateTime()
            val currentDateUtc = currentDate.withZone(DateTimeZone.UTC)
            val id = Posts.insert {
                it[title] = "Subscription &ndash; " + DateTime.now()
                    .toString(DateTimeFormat.forPattern(POST_TITLE_DATE_FORMATTER))
                it[author] = 1
                it[date] = currentDate
                it[dateGMT] = currentDateUtc
                it[postStatus] = "wc-active"
                it[postType] = "shop_subscription"
                it[content] = ""
                it[excerpt] = ""
                it[pinged] = ""
                it[toPing] = ""
                it[postContentFiltered] = ""
            }get Posts.id
            return@transaction id
        }
    }
kotlin-exposed ktor kotlin
1 Answers

In the end I solved by editing the id column as an entity column and by defining my table as an IdTable<Int>:

object Posts : IdTable<Int>("wpv1_posts") {
    override val id: Column<EntityID<Int>> = integer("ID").entityId()
    /// other columns, truncated for brevity
}

The insert becomes:

override fun createSubscription(metas: List<Meta>): Int {
    try {
        return transaction {
            val currentDate = DateTime()
            val currentDateUtc = currentDate.withZone(DateTimeZone.UTC)
            val id = Posts.insert {
                it[title] = "title"
            } get Posts.id
            transaction { // update the row just created
                Posts.update({ Posts.id eq id }) {
                    it[guid] = getGuid(id.value)
                }
            }
            return@transaction id.value
        }
    } catch (e: Exception) {
        return -1
    }
}
user_62751740
Related