Hi! Is it possible to execute with Exposed DSL a q...

# exposed

[agathis]

Hi!
Is it possible to execute with Exposed DSL a query like this one? Or will I have to resort to the raw query approach?
I can create the internal query, but how to filter it?

select id
from (select id, row_number() over (partition by col1 order by col2) rn from my_table)
where rn > ?

[Chantal Loncle]

This is well overdue, but if you're still looking for a way to generate the above SQL, here's an example:

object MyTable : Table("my_table") {
    val id = integer("id")
    val col1 = integer("col1")
    val col2 = integer("col2")
}

val windowDef = rowNumber().over().partitionBy(MyTable.col1).orderBy(MyTable.col2).alias("rn")

// alias the entire inner query so Exposed can use it with select ... from
val innerQuery = MyTable.slice(MyTable.id, windowDef).selectAll().alias("iq")

innerQuery
    .slice(innerQuery[MyTable.id])
    .select { windowDef.aliasOnlyExpression() greater longLiteral(1) }

// generates SQL:
SELECT iq.id
FROM (
    SELECT my_table.id,
        ROW_NUMBER() OVER(PARTITION BY my_table.col1 ORDER BY my_table.col2 ASC) rn
    FROM my_table
) iq
WHERE rn > 1