hudsonbay
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_allto achieve more performance - I’m not sure if I have the risk of a SQL injection here?
Maybe using Ecto macros it’s better
has anyone faced a similar problem in the past?
Marked As Solved
hudsonbay
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
Also Liked
stefanchrobot
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
Popular in Questions
What is the proper way to load a module from a file in to IEX?
In the python world, doing something like this pretty standard:
from ....
New
How to handle excepions in elixir? Suppose i have A, B, C ,D, E modules. and each module has get() function. A.get() method will call th...
New
In the Dialyzer docs ( http://erlang.org/doc/man/dialyzer.html#requesting-or-suppressing-warnings-in-source-files ), there is a way to tu...
New
Hi all,
Trying to get some more clarity over utc_datetime and naive_datetime for Ecto:
https://hexdocs.pm/ecto/Ecto.Schema.html#module-...
New
Hello, I have map which I want to convert it to string like this:
the map:
%{last_name: "tavakkoli", name: "shahryar"}
the string I ne...
New
I’m trying to make a websocket server in Phoenix or raw Elixir. I heard about gun, I think I could use cowboy, but since I’m not that sma...
New
Hi guys, i’m new in the Elixir world, and i have to say, that i love it!
i’m having some problem to understand anonymous functions with ...
New
Sometimes I want to check if the input into a function is not a blank string.
My first approach:
defmodule Example do
def do_stuff(s...
New
About me? ( if you have nothing better to do than reading about some random guy in the internet :stuck_out_tongue: )
Hello all, this is ...
New
I have a super simple question about elixir - how would I take a file like this
foo bar baz
and output a new file that enumerates th...
New
Other popular topics
Hello!
tl;dr Announcing Oban, an Ecto based job processing library with a focus on reliability and historical observability.
After spen...
New
I have a server on AWS, and was running a load test using artillery. When looking at the Phoenix dashboard I see the Ports going to 100% ...
New
Good day to you all.
I have been struggling to get a query involving like and ilike to work.
Can anyone assist me on this, please?
pro...
New
This post is a wiki (feel free to hit the edit button near the bottom right of this post to add your own changes!)
This post collects co...
New
I would like to know what is the best IDE for elixir development?
New
What’s the safe way to decode a JSON string into a struct? I want to avoid calling String.to_atom. Jason.decode can give me a map with st...
New
I have seen a lot of code which picks the first element from a list using Enum.at(0) instead of List.first. Is there a reason why people ...
New
Currently suffering from paralysis by [HTTP client] analysis. This is rather unusual in Elixirland as there tends to be consensus on the ...
New
I am constructing a JSON object (map) and I need to conditionally set a field. I’m trying to write proper elixir-way code… and I’m at a l...
New
I am trying to run a deploy with docker and I successfully runned with this command:
docker build -t romenigld/blog-prod .
but when I t...
New
Latest on Elixir Forum
Sponsor Spotlight
Build Elixir applications with speed and confidence.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
Enabling companies to succeed by building software people love.
Courses that'll move you from confusion to "Aha, now I get it!"