Ecto bulk delete in a many to many relation

hello, I have a sku_store many to many relation.

I’m trying to delete the relations in this list:

# 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? :thinking: Maybe using Ecto macros it’s better

has anyone faced a similar problem in the past?

I’m writing this from the top of the head, but you should be able to do this:

attrs = Enum.map(list, fn %{sku_id: sku_id, store_id: store_id} -> {sku_id, store_id} end)

Repo.delete_all(
  from s in SkuStore,
  where: {s.sku_id, s.store_id} in ^attrs
)
1 Like

Thanks a lot, @stefanchrobot
That’s a great approach :clap: 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 :+1:.

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

Using this answer Ecto IN clauses with tuples - #16 by alex_weinberger I could do it. So at the end, this is what I did:

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

I think I’ve hit this before too. I think this means you need to use explicit values (not even variable) to make this work.