https://kotlinlang.org logo
#exposed
Title
# exposed
a

Adam Crane

02/23/2021, 7:40 PM
Hi all, does anyone know how I could use Exposed to model a routine/function in MS SQL that takes in a varchar and returns a table? Ideally, I'd love to use the routine in a join statement. Worst case I'll use custom SQL, but was hoping there was a better way.
j

Joel

02/24/2021, 2:26 PM
That sounds like a case/when? What does the SQL look like?
a

Adam Crane

02/24/2021, 2:30 PM
A simplified version of the SQL looks like this:
Copy code
select a.* from account a
  join Routine('user_id') as rt on a.id = rt.id;
Basically the routine returns a list of account ids the user is allowed to access. Unfortunately, I have to use the routine and don't have access to the inner workings.
j

Joel

02/24/2021, 3:24 PM
Oh, the routine is in the database
No idea about that
Best bet might be a custom function
e

Endre Deak

02/25/2021, 5:12 PM
one trick I saw is that you define a table and override
describe
then you can join it. So something like
Copy code
object RoutineUserTable : Table() {    
    override fun describe(s: Transaction, queryBuilder: QueryBuilder) {
        queryBuilder.append("Routine('user_id')") 
    }
}
and this can be joined. I’ve seen this in one of the open github issues
a

Adam Crane

02/25/2021, 5:18 PM
That's very helpful, thanks. The only problem I have is that the
'user_id'
could be different each time I use the table.
e

Endre Deak

02/25/2021, 5:26 PM
you could figure out a way where you can extract
user_id
out as a parameter at a higher level, then create the table dinamically:
Copy code
fun createTable(param: String): Table() = object: Table() {
    override fun describe(...) {
        queryBuilder.append("Stuff(${param})")
    }
}
a

Adam Crane

02/25/2021, 6:03 PM
That's really interesting to think about. I'm wondering if I could actually handle this by creating a class instead of lots of objects like this:
Copy code
class FunctionBasedTable(private val inputValue: String) : Table("schema.Function('$inputValue')") {
	val outputValue = varchar("output_column", 10)
}
If anyone is interested, I found a hack to get a function, that returns a table, to work like an Exposed table
Copy code
class GetUserAccounts(private val userId: String, private val alias: String) : Table(alias) {
  val accountId = varchar("Account_Id", 18)

  override fun describe(s: Transaction, queryBuilder: QueryBuilder) {
    queryBuilder.append("schema.Get_User_Accounts('$userId') AS $alias")
  }
}

fun getIds(userId: String) : List<String> {
  return transaction {
    val table = GetUserAccounts(userId, "gua")
    table
      .selectAll()
      .withDistinct()
      .mapNotNull { row ->
        row[table.accountId]
      }
  }
}
This also works when it is used as a join table!
j

Joel

02/26/2021, 9:43 PM
Tricky tricky! Good find.