How to minimize inserts when creating many-to-many associations?

Questions / Help
, ,
1

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?

2

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?

3

Hello I guess I would go something like this

INSERT INTO statuses (text, sender_id, recipient_id, inserted_at, updated_at) VALUES ('Lorem ipsum dolor', 3, null, now(), now()) RETURNING id;
INSERT INTO mentions (user_id, status_id, inserted_at, updated_at) VALUES (1, 72, now(), now()), (2, 72, now(), now()), (3, 72, now(), now()) RETURNING id;

So, for one status and three mentions (total four records) we have two insert statements.

I could build those Mention schemas and persist them manually with Repo.insert_all(), after I persist the Status schema and get the {:ok, status} tuple. In that case that logic will have to go in the context module. A previous iteration did just that. But I really like the simplicity of assigning the related User schemas and let Ecto take care the rest.

4

I dont think insert_all builds a single INSERT INTO for multiple rows. Could you test it and share results if possible?

5

It does use a single insert


iex(10)> mentions =  Enum.map(1..3, &%{user_id: &1, status_id: 1, inserted_at: NaiveDateTime.utc_now()|> NaiveDateTime.truncate(:second), updated_at: NaiveDateTime.utc_now()|> NaiveDateTime.truncate(:second)})
# ... a list of maps
iex(11)> Repo.insert_all(TweetClone.Statuses.Mention, mentions)
[debug] QUERY OK db=13.7ms queue=0.3ms idle=1198.0ms
INSERT INTO "mentions" ("inserted_at","status_id","updated_at","user_id") VALUES ($1,$2,$3,$4),($5,$6,$7,$8),($9,$10,$11,$12) [~N[2020-06-02 19:58:52], 1, ~N[2020-06-02 19:58:52], 1, ~N[2020-06-02 19:58:52], 1, ~N[2020-06-02 19:58:52], 2, ~N[2020-06-02 19:58:52], 1, ~N[2020-06-02 19:58:52], 3]
{3, nil}