hello, i looked around but didn't find clues
how do i create a query with an IN ( ?, ? , ………) , @[element x, element x+1………, element x + n]
db.exec(sql"update table set <col> = ? where <col2> IN (?,)", el, @[])
i will work on a table with 200 000 lines, if i have to update them one by one i gonna lose hours …………
thank you for your time.
by the way, i started nim few days ago. so far so good. thx to the devs
I would say you should join() the ids into a string using join (from strutils). If the ids are int64s, then first convert them to either an array of string and then join them, or immediately to a string using a loop.
Also, take a look at Nexus which includes an ORM. You define your model in YAML, although I'm moving to Nim objects in a future version. Then you'd query with:
let idList = @[ 1, 2, 3, 4, 5 ]
filterMyTable(
myContext.db,
whereClause = "col in (?)",
whereValues = join(idList, ", "))
The filterMyTable() proc is generated and calls db_postgres for you, returning an object type that descibes a record for the myTable model (the my_table table). You could then call updateMyTableByPk() per record returned, updating on the primary key. This approach is a bit slow, because you first have to fetch the records, but it seems to be a common problem with ORMs.
I also plan on reworking this sort of proc to be more flexible (use of Nim objects), but the code will be backwards compatible.
When using nim's SQL generator you need to add the IN ()-values manually, since using the ? will quote integer-values inside (IN ('1','2','3')).
The example below uses a safe range of IDs, but if you are going to use external input for the IDs, then you need to validate the values manually before using them in join() procedure.
import
std/db_postgres,
std/strutils
let
rangeOfIDs = 1..100
valueToSet = "test"
db = connectToDB()
var
allIDs: seq[int]
for i in rangeOfIDs:
allIDs.add(i)
db.exec(sql("UPDATE table SET column = ? WHERE id IN (" & allIDs.join(",") & ")"), valueToSet)
thank you both, i'll go with ThomasTJdev's at the moment.
i was not far, i didn't dig enough to see sql**(<query>)** i saw only sql**"<query>"**
so i tried to join and insert with ? but an error occured. i also forgot about & and was thinking about trying with fmt but i buged on how to do fmtsql"< in ({join(",")})>
anyway now it's working thank you both for your time and nice answers
I created a tiny wrapper that would allow to use convenient SQL queries, example (it will be similar with your original question about "update where ...").
Nim
db.exec sql"""insert into users (name, age) values ({"Jim"}, {30})"""
let users = db.filter(sql"select name, age from users order by name", tuple[name: string, age: int])
assert users == @[(name: "Jim", age: 30)]
assert db.get_value(sql"select count(*) from users where age = {30}", int) == 1
It's not packaged properly, you may just copy/paste source code in your project.