hi, I am trying to do something specific in sqldel...

# squarelibraries

[Nikky]

hi, I am trying to do something specific in sqldelight jvm/postgres I know I can do WHERE (col1, col2) in ((1,2),(2,3),(5,6) but I cannot replace that list of tuples with ? or :pairs in sqldelight, any ideas/suggestions for how to work around that ?

PS I would love to have a minimal amount of boilerplate to stick raw SQL into with my own mapper to shore up the missing bits where necessary

[griffio]

There isn’t any built in support for this in SqlDelight to generate bind parameters like your example

I have shown the boiler plate for both - the sql string for binding multiple pairs would have to be generated ((?, ?), ...)- the example below is hard-coded to 2 pairs / 4 bindings

The only slight work around is to use unnested instead, so that only two bind parameters are needed - values for col1 and values for col2

import app.cash.sqldelight.ExecutableQuery
import app.cash.sqldelight.db.QueryResult
import app.cash.sqldelight.db.SqlCursor
import app.cash.sqldelight.db.SqlDriver
import app.cash.sqldelight.driver.jdbc.JdbcPreparedStatement
import app.cash.sqldelight.driver.jdbc.asJdbcDriver

import org.postgresql.ds.PGSimpleDataSource

fun getSqlDriver(): SqlDriver {
    val datasource = PGSimpleDataSource()
    datasource.setURL("jdbc:<postgresql://localhost:5432/postgres>")
    datasource.applicationName = "App Main"
    return datasource.asJdbcDriver()
}

fun main() {
    val driver = getSqlDriver()
    val sample = Sample(driver)

//    SELECT * FROM X;
//    col1 | col2
//    ------+------
//    1 |    2
//    3 |    4
//    5 |    6
//    (3 rows)

    val pairMapper: (SqlCursor) -> Pair<Int, Int> = { cursor ->
        cursor.getLong(0)!!.toInt() to cursor.getLong(1)!!.toInt()
    }

    val xQry = object : ExecutableQuery<Pair<Int, Int>>(pairMapper) {
        override fun <R> execute(mapper: (SqlCursor) -> QueryResult<R>): QueryResult<R> {
           return driver.executeQuery(-1, "SELECT * FROM X WHERE (col1, col2) IN ((?, ?), (?, ?))", mapper, 4) {
               bindLong(0, 3)
               bindLong(1, 4)
               bindLong(2, 5)
               bindLong(3, 6)
            }
        }
    }
    println(xQry.executeAsList()) // [(3, 4), (5, 6)]


    val xUnNestQry = object : ExecutableQuery<Pair<Int, Int>>(pairMapper) {
        override fun <R> execute(mapper: (SqlCursor) -> QueryResult<R>): QueryResult<R> {
            return driver.executeQuery(-1,
                "SELECT * FROM X JOIN UNNEST(?, ?) AS A(col1, col2) ON X.col1 = A.col1 AND X.col2 = A.col2", mapper, 2) {
                check(this is JdbcPreparedStatement)
                bindObject(0, arrayOf(3, 5))
                bindObject(1, arrayOf(4, 6))
            }
        }
    }
    println(xUnNestQry.executeAsList()) [(3, 4), (5, 6)]

[Nikky]

wow, what a monstrosity.. I will try it out but I don't think I'll stick with this solution