Ecto.Multi - insert two related records

Question

I am trying to insert two related records. When I try to create a new user. I would like to first create a record in the pools table and pass its' id to the users' table pool_id`. I am trying to use Ecto.Multi, but without any success. I would appreciate a guidance or any help! Thanks in advance! I have never used Ecto.Multi, hence, I would be grateful for the explanation. Errors at the end.

Here is my user schema:

  schema "users" do
    field :email, :string
    field :full_name, :string
    field :password_digest, :string
    belongs_to :role, EmployeeRewardApp.Role
    has_one :pool, EmployeeRewardApp.Points.Pool
    timestamps()
    # Virtual Fields
    field :password, :string, virtual: true
    field :password_confirmation, :string, virtual: true
  end

  @doc false
  def changeset(user, attrs) do
    user
    |> cast(attrs, [:full_name, :email, :password, :password_confirmation, :role_id])
    |> validate_required([:full_name, :email, :password, :password_confirmation, :role_id])
    |> cast_assoc(:pool)
end

Pool schema:

  schema "pools" do
    field :starting_points, :integer
    field :used_points, :integer
    belongs_to :user, EmployeeRewardApp.Accounts.User
    timestamps()
  end

  @doc false
  def changeset(pool, attrs) do
    pool
    |> cast(attrs, [:starting_points, :used_points])
    |> validate_required([:starting_points])
  end

And create function:

  def create(conn, %{"user" => user_params}) do
    alias Ecto.Multi
    Multi.new
    |> Multi.insert(:pool, %Pool{starting_points: 50, used_points: 0})
    |> Multi.merge(fn %{pool: pool} ->
      user_pool_relation_multi(pool.id, conn, %{"user" => user_params})
    end)
    |> Repo.transaction()
  end

  def user_pool_relation_multi(pool_id, conn, %{"user" => user_params}) do
    alias Ecto.Multi
    Multi.new
    |> Multi.insert(:user, User.changeset(%User{}, user_params))
    |> Repo.transaction()
  end

The error is now:

[error] #PID<0.1477.0> running EmployeeRewardAppWeb.Endpoint (connection #PID<0.1476.0>, stream id 1) terminated
Server: localhost:4000 (http)
Request: POST /users
** (exit) an exception was raised:
    ** (Postgrex.Error) ERROR 42703 (undefined_column) column "user_id" of relation "pools" does not exist

    query: INSERT INTO "pools" ("starting_points","user_id","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id"
        (ecto_sql 3.6.2) lib/ecto/adapters/sql.ex:760: Ecto.Adapters.SQL.raise_sql_call_error/1
        (ecto 3.6.2) lib/ecto/repo/schema.ex:725: Ecto.Repo.Schema.apply/4
        (ecto 3.6.2) lib/ecto/repo/schema.ex:350: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
        (ecto 3.6.2) lib/ecto/association.ex:814: Ecto.Association.Has.on_repo_change/5
        (ecto 3.6.2) lib/ecto/association.ex:554: Ecto.Association.on_repo_change/7
        (elixir 1.12.2) lib/enum.ex:2385: Enum."-reduce/3-lists^foldl/2-0-"/3
        (ecto 3.6.2) lib/ecto/association.ex:532: Ecto.Association.on_repo_change/4
        (ecto 3.6.2) lib/ecto/repo/schema.ex:873: Ecto.Repo.Schema.process_children/5
        (ecto 3.6.2) lib/ecto/multi.ex:716: Ecto.Multi.apply_operation/5
        (elixir 1.12.2) lib/enum.ex:2385: Enum."-reduce/3-lists^foldl/2-0-"/3
        (ecto 3.6.2) lib/ecto/multi.ex:690: anonymous fn/5 in Ecto.Multi.apply_operations/5
        (ecto_sql 3.6.2) lib/ecto/adapters/sql.ex:1017: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
        (db_connection 2.4.0) lib/db_connection.ex:844: DBConnection.transaction/3
        (ecto 3.6.2) lib/ecto/repo/transaction.ex:20: Ecto.Repo.Transaction.transaction/4
        (ecto 3.6.2) lib/ecto/multi.ex:696: Ecto.Multi.apply_operation/5
        (elixir 1.12.2) lib/enum.ex:2385: Enum."-reduce/3-lists^foldl/2-0-"/3
        (ecto 3.6.2) lib/ecto/multi.ex:690: anonymous fn/5 in Ecto.Multi.apply_operations/5
        (ecto_sql 3.6.2) lib/ecto/adapters/sql.ex:1017: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
        (db_connection 2.4.0) lib/db_connection.ex:1512: DBConnection.run_transaction/4
        (ecto 3.6.2) lib/ecto/repo/transaction.ex:20: Ecto.Repo.Transaction.transaction/4

Also, When I change starting_points: 50 to "starting_points" => pool.starting_points (so it is set from the form) it says:

key "starting_points" not found

Params:

user     %{"email" => "test.999@test.com", "full_name" => "Test Test", "password" => "Test", "password_confirmation" => "Test", "pool" => %{"starting_points" => "80"}, "role_id" => "1"}

As you can see in the schemas. I have table pools with stating_points and used_points, and users table with users' info and pool_id field. What I want to do is: When creating new user, insert new record to pools table with value of starting_points given in the form and used_points set to 0. Then insert other params into users table with newly created id in pools table. The error says that pool_id is not in pools table. This error and Ecto.Multi make me confused on how to create new user while also setting their starting points in pools table.

CreatePoolsMigration

  def change do
    create table(:pools) do
      add :starting_points, :integer
      add :used_points, :integer

      timestamps()
    end
  end

CreateUsers

  def change do
    create table(:users) do
      add :full_name, :string
      add :email, :string
      add :password_digest, :string

      timestamps()
    end

  end

AddPoolIDToUsers

  def change do
    alter table(:users) do
      add :pool_id, references(:pools)
    end
    create index(:users, [:pool_id])
  end

Answer 1

I see you have added a pool_id column to the users table. Now the error message you're facing is that a user_id column does not exist on the pools table.

Ecto believes that you have a user_id column in the pools table because of this line in your schema belongs_to :user, EmployeeRewardApp.Accounts.User.

the difference between belongs_to and has_one is that the belongs_to relationship goes into the schema which holds the foreign key (aka user_id in this case). Check this section of the Ecto docs on Hexdocs.pm. This seems like the correct direction for this relationship, so use a migration to remove pool_id from the users table and add user_id to the pools table.

Alternatively you could flip this around in the schema, and put that a user belongs_to a pool and that a pool has one user, but I think you'll find this is the unconventional way to relate the tables. Best advice is to stick with convention when possible, especially when learning new technologies.

With this strategy, you won't even need to use a Multi to insert the pool record, because you already have the ID number of the user, you can just insert one row into the pools table, instead of inserting a pool row and then getting the ID of that row and updating the users table.