Hi all, does anyone know how I could use Exposed to model a routine/function in MS SQL that takes in...

# exposed

[Adam Crane]

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.

[Joel]

That sounds like a case/when? What does the SQL look like?

[Adam Crane]

A simplified version of the SQL looks like this:

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.

[Joel]

Oh, the routine is in the database

[Joel]

No idea about that

[Joel]

Best bet might be a custom function

[Endre Deak]

one trick I saw is that you define a table and override describe then you can join it. So something like

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

[Adam Crane]

That's very helpful, thanks. The only problem I have is that the 'user_id' could be different each time I use the table.

[Endre Deak]

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:

fun createTable(param: String): Table() = object: Table() {
    override fun describe(...) {
        queryBuilder.append("Stuff(${param})")
    }
}

[Adam Crane]

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:

class FunctionBasedTable(private val inputValue: String) : Table("schema.Function('$inputValue')") {
	val outputValue = varchar("output_column", 10)
}

[Adam Crane]

If anyone is interested, I found a hack to get a function, that returns a table, to work like an Exposed table

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!

[Joel]

Tricky tricky! Good find.