Hi all, does anyone know how I could use Exposed t...
# exposed
a
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
That sounds like a case/when? What does the SQL look like?
a
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
Oh, the routine is in the database
No idea about that
Best bet might be a custom function
e
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
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
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
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
Tricky tricky! Good find.