CODEWITHSUNDEEP
sqlhaskellyesodpersistentrawsql
Geradlus_RU
Geradlus_RU

Reputation: 1478

`?` 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

Upvotes: 2

Views: 626

Answers (2)

Justin Bailey
Justin Bailey

Reputation: 1567

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).

Upvotes: 1

Ed&#39;ka
Ed&#39;ka

Reputation: 7138

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.

Upvotes: 1

Related Questions