kotlinktorkotlin-exposed

Getting autogenerated id after insert - ktor exposed


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
        }
    }

Solution

  • 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
        }
    }