# This map is what I'm sending to the function
[%{sku_id: 1, store_id: 2}, %{sku_id: 3, store_id: 4}]
(In reality there are like 10_000 registries to delete in bulk) and there’s no primary key, BTW.
This function does the job:
def bulk_delete_skus_in_stores(list) do
keys = [:sku_id, :store_id]
attrs = list_to_tuple(list, keys)
Repo.query(~s"""
delete from sku_store where (#{keys |> Enum.join(",")}) in (#{attrs})
""")
|> case do
{:ok, %Postgrex.Result{num_rows: num_rows}} -> %{rows_affected: num_rows, errors: []}
end
end
The problem with this function is:
It does the job one by one. I want to use a delete_all to achieve more performance
I’m not sure if I have the risk of a SQL injection here? Maybe using Ecto macros it’s better
Thanks a lot, @stefanchrobot
That’s a great approach and it’s more or less what I’ve been trying to do with no success.
In this case, I would have something like this [{1, 2}, { 3, 4}] to compare with .
But it gives me now an error that I’m still trying to debug and have no idea why it’s happening. It makes no sense to me:
(Ecto.Query.CompileError) Tuples can only be used in comparisons with literal tuples of the same size
Why it’s telling me tuples are not the same size? Makes no sense
def delete_skus_by_id(list) do
queryable =
from s in SKUStore,
inner_join:
j in fragment(
"SELECT DISTINCT * FROM jsonb_to_recordset(?) AS j(sku_id int, store_id int)",
^list
),
on: s.sku_id == j.sku_id and s.store_id == j.store_id
Ecto.Multi.new()
|> Ecto.Multi.delete_all(:delete_all, queryable, on_conflict: :nothing)
|> Repo.transaction()
|> case do
{:ok, _} ->
%{rows_affected: length(list), errors: []}
{
:error,
_,
%Ecto.Changeset{
changes: changes,
errors: errors
},
_
} ->
%{
rows_affected: 0,
errors: "Changes #{changes} couldn't be created because #{inspect(errors)}"
}
end
end