voger
How to minimize inserts when creating many-to-many associations?
I am writing a twitter clone project to see how it is done. I want to be able to create mentions to users from the post. I have these schemas
defmodule TweetClone.Accounts.User do
use Ecto.Schema
defmodule TweetClone.Accounts.User do
use Ecto.Schema
schema "users" do
field :nickname, :string
# ... other things
many_to_many :mentioned_statuses, Status, join_through: TweetClone.Statuses.Mentions
end
end
defmodule TweetClone.Statuses.Mention do
use Ecto.Schema
import Ecto.Changeset
schema "mentions" do
belongs_to :user, TweetClone.Accounts.User
belongs_to :status, TweetClone.Statuses.Status
timestamps()
end
def changeset(%__MODULE__{} = mention, %{user: user, status: status}) do
mention
|> change()
|> put_assoc(:user, user)
|> put_assoc(:status, status)
end
end
defmodule TweetClone.Statuses.Status do
use Ecto.Schema
schema "statuses" do
# ... various fields
many_to_many :mentioned_users, User, join_through: TweetClone.Statuses.Mention
end
def changeset(status, attrs) do
status
# ... various pipes
|> mention_users()
end
defp mention_users(changeset) do
text = get_change(changeset, :text, "")
mentioned_nicknames =
Regex.scan(~r/@[\w.@_-]+/u, text)
|> Enum.map(fn nickname ->
nickname
|> hd()
|> String.trim_leading("@")
end)
mentioned_users = Repo.all(from u in User, where: u.nickname in ^mentioned_nicknames)
put_assoc(changeset, :mentioned_users, mentioned_users)
end
end
This works but when I do in iex
iex(49)> attrs = %{sender: user1, text: "Hello, @john2 @john3"}
iex(50)> Statuses.create_status(attrs)
[debug] QUERY OK source="users" db=2.8ms queue=0.2ms idle=1988.4ms
SELECT u0."id", u0."nickname", u0."email", u0."password_hash", u0."confirmed_at", u0."reset_sent_at", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."nickname" = ANY($1)) [["john2", "john3"]]
[debug] QUERY OK db=4.2ms queue=0.3ms idle=1991.8ms
begin []
[debug] QUERY OK db=6.4ms
INSERT INTO "statuses" ("sender_id","text","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" [1, "Hello, @john2 @john3", ~N[2020-05-31 17:41:42], ~N[2020-05-31 17:41:42]]
[debug] QUERY OK db=9.5ms
INSERT INTO "mentions" ("status_id","user_id","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" [71, 2, ~N[2020-05-31 17:41:42], ~N[2020-05-31 17:41:42]]
[debug] QUERY OK db=2.7ms
INSERT INTO "mentions" ("status_id","user_id","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" [71, 3, ~N[2020-05-31 17:41:42], ~N[2020-05-31 17:41:42]]
[debug] QUERY OK db=3.2ms
commit []
{:ok,
%TweetClone.Statuses.Status{
...
It performs one insert for the Status and two other inserts for the Mention rows. Is there any way to minimize those insert operations?
First Post!
thojanssens1
Let’s put Elixir/Ecto aside for a moment and talk SQL only. We see now 3 “INSERT INTO” queries to perform that operation.
How would you minimize these queries with SQL?
Popular in Questions
Hello all!
I am typing this post from my new MacBook Pro with the M1 chip. I’m loving it so far, and will probably use it as my daily dr...
New
We have an ECS cluster with 4 services, where each task joins a single cluster, via discovery ECS discovery service.
Currently when I de...
New
Hey,
I have a NanoPi-M3 and try to install Elixir on their Ubuntu image. I followed the Raspberry Pi installation instructions from the ...
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
I have a User schema with a :from_id field set to type :string:
defmodule TweetBot.Repo.Migrations.CreateUsers do
use Ecto.Migration
...
New
I tried installing
elixir 1.11.2
erlang 23.3.4
via asdf in my zsh shell. Enabled the versions locally and globally.
When I list them ...
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
After calling mix ecto.create I get this error:
17:00:32.162 [error] GenServer #PID<0.412.0> terminating
** (Postgrex.Error) FATAL...
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
Hi everyone!
I need implement if…else if…else condition from my elixir code, and anymore of this control flow structures not work proper...
New
Other popular topics
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
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
As the title describes, I’m trying to run Enum.map() over a list of key/value pairs, where the value is a map. My data looks like this:
...
New
I couldn’t find any guides that worked well with Phoenix 1.6.0 and esbuild. I hope this helps people test the waters and eases you into t...
New
I wanted to check elixir version in phoenix because i found that my elixir is 1.5 but when i use Enum.chunk_by it said the function is un...
New
Hi,
I'm quite new in Elixir and I'm trying to format a string to a PEM format. I have the certificate value like MIIDBTCCAe2...... and ...
New
The Elixir Typespec docs show the following syntax for keyword lists in typespecs:
# ...
| [key: type] # keyword lis...
New
After calling mix ecto.create I get this error:
17:00:32.162 [error] GenServer #PID<0.412.0> terminating
** (Postgrex.Error) FATAL...
New
We’ve put together this wiki for Phoenix LiveView - please feel free to add any info you feel is worth including.
What is Phoenix LiveV...
New
Elixir appears to be a superior language to Python. I don’t see any advantage of Python over Elixir. Are there any?
New







