'Argument error' when trying a delete query with 'in ( ... )'

[gtaranti]

I try this code :

records = [139937, 139935]

result = MyXQL.query(conn, "delete from log where id in (?)", records)

and get back error

** (ArgumentError) expected params count: 1, got values: [139937, 139935] for query: %MyXQL.Query{name: "", cache: :reference, num_params: 1, ref: #Reference<0.332169346.1053818882.146638>, statement: "delete from log where id in (?)", statement_id: 11}
    (myxql 0.6.3) lib/myxql/query.ex:98: DBConnection.Query.MyXQL.Query.encode/3

When I try without only in element in the list, it works :

records = [139937]

result = MyXQL.query(conn, "delete from log where id in (?)", records)

{:ok,
 %MyXQL.Result{
   columns: nil,
   connection_id: 204256,
   last_insert_id: 0,
   num_rows: 1,
   rows: nil,
   num_warnings: 0
 }}

Is there a special syntax for more than one elements in the list?

[wojtekmach]

The third argument to query is not a param, it's a list of parameters. That's why sending a single ID seemingly worked. So the invocation should rather be:

ids = [139937, 139935]
MyXQL.query(conn, "delete from log where id in (?)", [ids])

It still won't work as expected since mysql neither has array type nor supports sending them over the wire. It does support JSON and Elixir lists and maps are encoded as such:

iex> MyXQL.query!(pid, "SELECT ?", [[1, 2]]).rows
[["[1,2]"]]

Starting in mysql server 8.0.17, you can write the equivalent of WHERE id IN ? for json arrays, WHERE id MEMBER OF(?):

ids = [139937, 139935]
MyXQL.query!(pid, "DELETE FROM logs WHERE id MEMBER OF(?)", [ids])

Personally I think I'd go with building the query manually, as explicit as possible so there are no security holes:

MyXQL.query!(pid, "DELETE FROM logs WHERE id in (" <> Enum.map_join(ids, ",", &Integer.to_string/1) <> ")")

[gtaranti]

Thanks! It's clear now.

You've been very helpful!!