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