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

Question

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

It allows to convert resulting data into Entity value on the fly — Geradlus_RU, Jan 16 '19 at 13:35
Typically however you can use more advanced functions for such requests, like `selectList [RequestId <-. []] []`. This will construct the query for you, and make sure it is converted to the proper entities. — Willem Van Onsem, Jan 16 '19 at 13:40
Dear Willem, I have to use `rawSql` because I have to join 3 tables in real code. In past I'd use `esqueleto`, but since I use latest LTS Stackage it is not possible — Geradlus_RU, Jan 16 '19 at 14:02

score 1 · Accepted Answer · answered Jan 17 '19 at 08:21

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.

score 1 · Answer 2 · answered Jun 03 '21 at 18:16

The solution is to not use IN, but the more flexible ANY function:

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

(PersistArray makes sure persistent renders the list using array literals. toPersistValue alone gives a syntax error).

You can read more about the syntax at https://www.postgresql.org/docs/13/functions-comparisons.html (look at the ANY/SOME (array) section).

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

2 Answers2