sqlhaskellyesodpersistentrawsql

`?` placeholder for SQL `IN` condition with persistent's `rawSql`


I would be happy to use ? placeholder to populate ids for SQL IN clause. Unfortunately the following does not work

let idList :: [ RequestId ]
    idList = []
let sql :: String
    sql =  "SELECT ?? FROM request WHERE request.id IN ?"
rs <- runDB $ rawSql sql [ toPersistValue idList ]

Such code results in database error similar to:

syntax error at or near "'[283,282,281]'"

Wrapping ? placeholder with brackets (e.g. IN (?)) gives another type of error:

invalid input syntax for integer: "[283,282,281]"

Is there a way to accomplish this?

P.S. Looks like it's a horrible title, have no idea how to improve that


Solution

  • I don't think there is a way to do it with persistent. postrgresql-simple (assuming that we are talking about Postgres here) which is used by persistent does have special In construct which is correctly translated into In (..) in SQL, but persistent doesn't seem to use it. One may hope that a workaround is to use PersistDbSpecific constructor which takes ByteString as an argument (so we could manually render and pass something like (123,456,789)) but unfortunately it is converted into SQL via Unknown which is then rendered via Escape which not only escapes the string but also encloses it into quotes which makes our SQL invalid. If persistent were using Plain (which in my view would make much more sence) this approach would work, but unfortunately it is not the case.