Hi. I need some help to map a SQL query for a 'greatest-n-per-group' problem to exposed DSL. It uses several JOIN with some subselect, and I have no idea where to start to translate this to the DSL. Here is the raw query. Any hints will be appreciated 🙂

SELECT p.id, p.title
FROM Publication p
JOIN ( 
	 SELECT psc.publicationId, psc.publicationStateId ,max(psc.id) 
	 FROM PublicationStateChange  psc
	 GROUP BY psc.publicationId
	) lastpsc ON p.id = lastpsc.publicationId and lastpsc.publicationStateId = ps.id
JOIN PublicationState ps ON ps.id = lastpsc.publicationStateId

WHERE ps.tag = "new"
;

@tapac I was thinking about adding some missing Javadoc and also fixing the code formating which in some places is crazy. And I wanted to know if I should make one big PR for the documentation and one for the formating, multiple PR in each case. Also about formating, I think there should be some guidelines about what code style contributors should follow and maybe committing files in folders

.idea/codeStyles/

and

.idea/inspectionProfiles/

Can i use exposed with mongodb?

Hello. Lets say, i need to reset in-memory database before each test. Reset - delete all existed rows and add default ones. But the problem is that after I call

deleteAll()

the PK value is not reseted to 0. Is there a way to do it?

Hi guys, is it any possibility to call stored procedure from exposed?

This message was deleted.

I tried by using Hikari (used by Google in their samples to connect a client to Cloud SQL, but I got nothing. I think it might be related to the fact the driver relies on Jetty and I’m deploying the ktor backend via appengine, who relying on`io.ktor.server.servlet.ServletApplicationEngine`. I don’t have plenty experience on the backend side of things, I’m not sure what I just said makes sense to you. I might mention this in the ktor channel as well.

hey everyone. is there anyone who met exception

Invalid argument value: <http://java.io|java.io>.NotSerializableException. Statement(s): null

when using Exposed DAO? i got this when tried to create

val created = User.new {
    username = "Ray Eldath"
    email = "<mailto:xxx@xxx.com|xxx@xxx.com>"
}

Authorization.new {
    user = created
    hashedPassword = hp.toBlob()
    role = UserRole.Root
}

in which

Authorization.id

is refer to

User.id

. any idea on this exception? many thanks. 🙃

Hi Can i create dao class (entity) for ordinary table (not IdTable)? Table definition:

object EventTable : Table("event") {

    val id: Column<String> = varchar("id", 255)
    val eventType: Column<EventType> = enumerationByName("type", 255, EventType::class)

    val event: Column<Event> = jsonb("event", objectMapper)

    override val primaryKey = PrimaryKey(id, eventType)
}

Hi huys, I am a bit confusing, I am trying to use BatchInsert in exposed, however amount of insert that loger shows are equals to the size of collection that I am trying to insert. I visited https://github.com/JetBrains/Exposed/issues/617 and still could not get answer, what should I do in order to make work batchInsert. Is separate inserts that exposed logger shows is okay? Has someone experience with batchInsert statement?

Further testing indicates that the Sql DSL triggers the problem, while using the Dao DSL does not. 110 seconds to insert all my data with the Sql DSL, 25 seconds with the Dao DSL.

can exposed verify that the db it connected to matches the schema ? i plan to use harmonica for migrations, but obviously need to make sure those migrations really work

Is it possible to set sqlite pragma when using exposed. I tried calling exec but it throws an exception "Query returns results"

Hi guys, Is there something that I can use for the following query

update some_table set is_deleted = true where id in (:some_list)

Hi, is there a way to select where some raw query match? I am looking for something like E.g

Products.select{ whereRaw("where array_to_string('some_psql_array_column') LIKE '%search%'") }

CC @tapac

Hello! Can someone help me setup a primary key column "id" with the expression

nextval('job_info_seq')

I have an integer database column that stores 0/1 for boolean false/true. Is there already a ColumnType configuration I can use for this? If not, should I create a new ColumnType? TIA

@tapac what is the difference between

LiteralOp

and

QueryParameter

?

LiteralOp

converts the specified

value

to a SQL value using the specified

columnType

, and

QueryParameter

, when the query uses a prepared statement, adds a

?

for each value and then injects the actual value, and when it is not prepared, just works like the

LiteralOp

?

Hi @tapac can I cache DAO objects, change it outside a transaction and then time to time update the changes in the DAO in de database? (cache in memory) Have someway to do that?

Hello. I use h2, have varchar column and want update column: concatenate current value and additional value. For example, current value of column ",1,2," and I want add "3," and get finally ",1,2,3,". I try to use code like this from documentation (but in documentation it's integer column).

Lessons.update(where = { Lessons.id eq lessonId }) {
            with(SqlExpressionBuilder) {
                it.update(column, column + "$entityId$ELEM_COLLECTION_SEPARATOR")
            }
        }

But it throws exception

Caused by: org.h2.jdbc.JdbcSQLFeatureNotSupportedException: Feature not supported: "VARCHAR +"; SQL statement:
UPDATE LESSONS SET CAME_STUDENTS=LESSONS.CAME_STUDENTS+? WHERE LESSONS.ID = ?

Exposed version 0.20.3. Oh, sorry to bother, I understood, that I should just use SqlExpressionBuilder.concat(column, stringLiteral("$entityId$ELEM_COLLECTION_SEPARATOR"))

is there any way to do

Database.connect

without registering the database globally ? is there a alternative to the

ThreadLocalTransactionManager

?

Hi @tapac why I can't access the values from my DAO outside a transaction? I'm getting a exception when a try to access

java.lang.IllegalStateException: No transaction in context.
    at org.jetbrains.exposed.sql.transactions.TransactionManager$Companion.current(TransactionApi.kt:92) ~[nations-spigot-0.1.0-SNAPSHOT-all.jar:?]
    at org.jetbrains.exposed.dao.EntityClass.warmCache(EntityClass.kt:24) ~[nations-spigot-0.1.0-SNAPSHOT-all.jar:?]
    at org.jetbrains.exposed.dao.EntityClass.testCache(EntityClass.kt:73) ~[nations-spigot-0.1.0-SNAPSHOT-all.jar:?]
    at org.jetbrains.exposed.dao.EntityClass.findById(EntityClass.kt:42) ~[nations-spigot-0.1.0-SNAPSHOT-all.jar:?]
    at org.jetbrains.exposed.dao.EntityClass.findById(EntityClass.kt:33) ~[nations-spigot-0.1.0-SNAPSHOT-all.jar:?]
    at org.jetbrains.exposed.dao.Entity.getValue(Entity.kt:55) ~[nations-spigot-0.1.0-SNAPSHOT-all.jar:?]
    at com.zentrymc.nationsapi.database.tables.Account.getSettings(Accounts.kt) ~[nations-spigot-0.1.0-SNAPSHOT-all.jar:?]

is there any example how to join over 3 tables in exposed? tables similar to this: https://stackoverflow.com/questions/8057656/join-over-three-tables-in-sql

i seemingly have issues with a latin1 collate on a mysql database, i am translating some stuff from phph (that i not fully understand) but it seems like in exposed i get the latin1 encoded strings instead of decoded to utf8 is there any options i stil need to set in exposed ?

Hi all, I have a simple table called users with the following columns id bigserial primary key, age integer something like

select max(age) from users

How can I get the eldest users with exposed?

Hi All, how can I write these query in exposed

select *
from users
where age = (select max(age) from users)

anyone who has the experience using exposed

batchinsert

?

Getting some weird behavior here. I've recently introduced HikariCP as a new DataSource to make use of connection pooling. The service was running smoothly for a few days, then suddenly, any call sprayed the exception in the attached snippet all over the place. Restarting the service immediately started to do the same, only rolling it back to an earlier version without HikariCP fixed the issue. Any idea where to start looking?

Another question - I am right now representing a JSON column in the DB as an exposed BLOB column. How can I properly insert this?