voger

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

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?

Where Next?

Popular in Questions Top

albydarned
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
Harrisonl
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
Phillipp
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
minhajuddin
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
chensan
I have a User schema with a :from_id field set to type :string: defmodule TweetBot.Repo.Migrations.CreateUsers do use Ecto.Migration ...
New
fayddelight
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
shahryarjb
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
Patoshizzle
After calling mix ecto.create I get this error: 17:00:32.162 [error] GenServer #PID<0.412.0> terminating ** (Postgrex.Error) FATAL...
New
9mm
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
lanycrost
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 Top

yawaramin
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
JorisKok
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
aalberti333
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
sergio
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
lastday4you
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
vac
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
myronmarston
The Elixir Typespec docs show the following syntax for keyword lists in typespecs: # ... | [key: type] # keyword lis...
New
Patoshizzle
After calling mix ecto.create I get this error: 17:00:32.162 [error] GenServer #PID<0.412.0> terminating ** (Postgrex.Error) FATAL...
New
AstonJ
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
Nvim
Elixir appears to be a superior language to Python. I don’t see any advantage of Python over Elixir. Are there any?
New

We're in Beta

About us Mission Statement