Hi everybody, any kind of news about task on the project roadmap?

What is the Equivalent of SQL

exists

in Kotlin Exposed? https://stackoverflow.com/questions/64245773/equivalent-of-sql-exists-in-kotlin-exposed

Hi 🙂 I am having a difficulty with the join mechanism: apparently if you hold a reference to a table and you join them

object UserQuestionTable : LongIdTable("user_question") {
    val uid = reference("uid", UserTable, onDelete = ReferenceOption.CASCADE)
    val oid = optReference("oid", OtherTable)
    val allow = bool("allow").default(true)
}

but you have a complex join that is not just the reference ids equal

UserQuestionTable.join(OtherTable, JoinType.LEFT, additionalConstraint = {
(OtherTable.id inList otherIds) and (
                (OtherTable.id eq UserQuestionTable.oid) or (
                    UserQuestionTable.oid.isNull() and (UserQuestionTable.allow eq true)
                    )
                )
        })

but the output query is added a

user_question.oid = other.id

and then the rest of the additional constraint query what can I do to remove this addition?

Hi guys, is there any particular reason for entity to be inside transaction block if i want to know id value in reference column? For example

entity.parent?.id?.value

code will throw

No transaction in context

although entity table has parent_id column and it should know parent id value when you fetched it, so no additional queries needed to DB. And exposed seems to know that, cause if i put this code under transaction block - parent id will be returned without any additional queries

Does anyone know how to return a value from a transaction while having a try/catch to handle errors? I posted about in this issue here, because the only way I've found to work around this is by having a separate variable and assigning to that, which is pretty gross.

var a = -1
newSuspendedTransaction {
    try {
        a = Users.insert {
            it[username] = model.username
        } get Users.userId
    } catch (e: Exception){

    }
}

return a

Any ideas as to how I can return the value from the transaction while also handling error? I've tried handling the error outside the transaction, but for some reason, transactions don't let errors bubble up

🎉Exposed 0.28.1 was released🎉 There are a few broken changes and a lot of bug fixed. Please check change log before update (at least Broken Changes section)

Hi, so I was trying this replace code in PostgreSQL and it seems that the on conflict only takes the primary key unique constraints

override fun replace(
    table: Table,
    data: List<Pair<Column<*>, Any?>>,
    transaction: Transaction
): String {
    val builder = QueryBuilder(true)
    val sql = if (data.isEmpty()) {
        ""
    } else {
        data.appendTo(builder, prefix = "VALUES (", postfix = ")") { (col, value) -> registerArgument(col, value) }.toString()
    }

    val columns = data.map { it.first }

    val def = super.insert(false, table, columns, sql, transaction)

    val uniqueCols = table.primaryKey?.columns
    if (uniqueCols.isNullOrEmpty()) {
        transaction.throwUnsupportedException("PostgreSQL replace table must supply at least one primary key.")
    }
    val conflictKey = uniqueCols.joinToString { transaction.identity(it) }
    return def + "ON CONFLICT ($conflictKey) DO UPDATE SET " + columns.joinToString { "${transaction.identity(it)}=EXCLUDED.${transaction.identity(it)}" }
}

is this intentional? why not include all unique indices ? I can try fixing it with a PR if needed 🙂

Hi! I'd assume the same `Table`s can't be used for both DAO and DSL variants...? Especially if there are relations to other tables... or maybe I'm missing something?

Does exposed use reflection at all? Would it be compatible with GraalVM?

Hey, is there some way to attach data to the transaction when it is started that I could access within an EntityHook? I'd like to make a function like

userTransaction(userId: Long, db: Database? = null, statement: Transaction.() -> T)

, but I can't find anywhere to store data on the Transaction object, or any way to subclass transaction and use that. Any suggestions?

Hey, is it right that exposed is not usable with JS?

Hi question about auto-fill created and updated columns: It seems like if I don’t use the Entity to update the table (using the DSL API) then the updated field is not treated As I understand it Entity hooks only apply to entity related changes and not to changes directly to the table using the DSL API. is there a way to do the same when using the DSL API to also auto-fill the updated column?

Hi! Is it possible to use custom column with custom alias in exposed query? I mean column that is not in Table definition. I need something like this:

SELECT 'custom1' as custom, id, name, address FROM persons

Hi, there is any way to create kotrin coroutine flow from exposed query?

What is the best practice for DB migrations with Exposed? I want to use Flyway, but I'm having trouble seeing how it doesn't defeat the purpose of an ORM in some ways. Meaning, if I add Flyway, I have to add all the table definitions into a file and a upscript every time I want to change the schema along with modifying the Exposed table models. Is there an better way to do this? I thought I might be able to use the Flyway API and run the migrations after I create all of the tables with Exposed, but I'm having trouble getting this to work because Flyway wants to have table definitions in its history. Does anyone have experience with this, or best practice recommendations? At the end of the day, I'm just trying to add persistent data to my database without having it repeatedly inserted (i.e., every time I run the application, which is what a

transaction

would do in Exposed). If there is another tool other than Flyway that might be better suited for this, then I'd be happy to hear about that as well.

val fullName: String get() = listOfNotNull(firstName, lastName).joinToString(" ")

    private var cachedFullName by UserDetailsTable.fullName

    companion object : EntityClass<String, UserDetailsView>(UserDetailsTable) {
        init {
            EntityHook.subscribe { action ->
                action.toEntity(this)?.let { it.cachedFullName = it.fullName }
            }
        }
    }

Is there a cleaner way to set a computed value using the DAO?

Hey, is there a way to force load all results from a

SELECT

with all relations to memory?

What's the best way to wait to execute a

transaction

? I want to take one in as a parameter in a function, and then execute the transaction. However, I've only ever seen it in the inline style of

transaction {...}

and having it execute there

is it possible to prefix generated sql with a comment for tracking?

Is there a way to dynamically modify tables? Instead of adding a new column in the table definition, can I do something like

transaction{
   Table.addColumn(...)
}

?

Is it possible to see if

insertIgnore

actually failed or not ?

Hi! We’re starting to use Kotlin with Exposed at my company, and one thing I’m interested in is enforcing that tables and fields are documented. I noticed that there’s a PR open about including SQL comments as a feature (with e.g.

varchar("name").comment("The customer's first name")

), but there wasn’t much support for it. Can anyone here think of any good/idiomatic approaches, or is there an official one that I’ve missed?

Something like

OrderItems.deleteWhere { OrderItems.id inSubQuery ( OrderItems.innerJoin(Orders).innerJoin(Items).slice(Orders.id).select { Orders.status eq 0 and (Items.alias eq "String") } ) }

(untested) would probably work as a workaround. It seems that deleteWhere is only available from a

Table

, not a

Join

or their superclass

ColumnSet

. The delete implementation does not seem to support DELETE <TableName> FROM …:

package org.jetbrains.exposed.sql.vendors // Default.kt
open fun delete(
        ignore: Boolean,
        table: Table,
        where: String?,
        limit: Int?,
        transaction: Transaction
    ): String {
        // ...
        return buildString {
            append("DELETE FROM ")
            append(transaction.identity(table))
            if (where != null) {
                append(" WHERE ")
                append(where)
            }
            // ...
        }
    }

What is the best practice, when It comes to returning a result from a query?

fun getSymbolId(symbol: String): Int {
    transaction {
        Symbols.select { Symbols.symbol eq symbol }.toList().first()
    }
}

is there a sample project available which combines exposed with spring, webflux, and coroutines?

Hello, can anyone help with how I might handle timeouts/ errors when I try and connect to an offline database using the exposed framework > Database.connect

Hi guys, How can I dynamically determine (on run time) which column in the table insert a value? In my issue, every transaction, I insert/update different columns…

is there any way to make a DAO Table with a compound PK? If not, can I just use a normal table and Mix the DAO IdTables and normal tables?

Hi all, I am using Kotlin with Exposed which is working. Can I also use it with JVM? Does anybody have a running example for database connection and query database?

Hello. Should all the examples on this page be inside a

transaction { }

block? https://github.com/JetBrains/Exposed/wiki/DSL