0

Is it possible to do something like this in Exposed to check the existence of a row? SELECT EXISTS(SELECT 1 FROM table WHERE...)

F. Caron
  • 638
  • 7
  • 19

2 Answers2

0

There is exists function in Exposed:

FooTable.select { 
   exists(BarTable.select { BarTable.id eq FooTable.id })
}
Tapac
  • 1,889
  • 1
  • 14
  • 18
  • Thanks for your response (I really apreciate your work with Exposed by the way). I was already aware of this function. But I am trying to return a boolean representing the existance of a single or multiple rows. In SQL we can `SELECT`the `Exists` expression directly so that it return its boolean value. Your exemple would map to something like : `SELECT * FROM FooTable WHERE EXISTS(...)` which returns elements from FooTable meeting the Exists condition. I am looking for something like this: `SELECT EXISTS(...)` which returns the result of the Exists statement directly. – F. Caron Aug 10 '19 at 13:42
  • Try to store your expression into variable like : `val extistsOp = exists(BarTable.select { BarTable.id eq FooTable.id }) FooTable.slice(existsOp).select{ ... }` – Tapac Aug 11 '19 at 14:38
  • Tried it, but I don't get a boolean when the row does not exist. My initial solution was to use `FooTable.slice(intLiteral(1)).select { ... }.count() > 0`. I think I'll stick with that for now. – F. Caron Aug 12 '19 at 15:44
0

You can achieve it by calling QueryBuilder, but I wish I could use more DSL descriptions.

val result: Int = transaction {
    addLogger(StdOutSqlLogger)

    val qb = QueryBuilder(false).append("SELECT ").append(
        exists(FooTable.select { FooTable.id eq 111 })
    )

    TransactionManager.current().exec(qb.toString()) {
        it.next()
        it.getInt(1)
    } ?: 0
}
Miyamoto_Ta
  • 21
  • 2