Hello!

I have a Phoenix v1.7.2 service and I am trying to do the following with Ecto and a Postgres datatabase.

I have simplified the code a little bit for brevity.

lib/helloworld/user.ex

defmodule HelloWorld.User do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :binary_id, autogenerate: false}
  @foreign_key_type :binary_id

  schema "users" do
    belongs_to :company, Company

    timestamps()
  end

  @doc false
  def changeset(user, attrs) do
    user
    |> cast(attrs, [:id])
    |> validate_required([:id])
  end
end

priv/repo/migrations/20211023161717_create_users.ex

defmodule HelloWorld.Repo.Migrations.CreateUsers do
  use Ecto.Migration

  def change do
    create table(:users, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :company_id, :binary_id

      timestamps()
    end

    create index("users", [:company_id])
  end
end

lib/helloworld/company.ex

defmodule HelloWorld.Company do
  use Ecto.Schema
  import Ecto.Changeset

  alias HelloWorld.User

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id

  schema "companies" do
    field :name, :string

    has_many :user, User
    belongs_to :owner, User

    timestamps()
  end

  @doc false
  def changeset(company, attrs) do
    company
    |> cast(attrs, [:owner_id, :name])
    |> validate_required([:owner_id, :name])
  end
end

priv/repo/migrations/20211023161718_create_companies.ex

defmodule HelloWorld.Repo.Migrations.CreateCompanies do
  use Ecto.Migration

  def change do
    create table(:companies, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :owner_id, references(:users, type: :binary_id, on_delete: :nothing), null: false
      add :name, :string, null: false

      timestamps()
    end

    create index("companies", [:owner_id])
  end
end

priv/repo/migrations/20211023161719_add_companies_users_association.ex

defmodule HelloWorld.Repo.Migrations.AddCompaniesUsersAssociation do
  use Ecto.Migration

  def change do
    alter table(:users, primary_key: false) do
      modify :company_id, references(:companies, type: :binary_id, on_delete: :nothing)
    end
  end
end

In summary for the db structure:

• I have a companies table
• I have a users table
• Each company can have many users as members
• Each company can have only ONE user as owner
• Each user can belong to ONE company

In my scenario, a user might already exists in my database with users.company_id null. At some point a user creates a company, and what I wish to achieve is to 1) insert the company in the dabatae 2) the user in users table, that matches the inserted company’s owner_id field, to be updated and get the company_id of the newly inserted company.

I only have this at the moment and not sure what to change, even though I tried plenty of things,

alias HelloWorld.Company

def create_company(attrs \\ %{}) do
    %Company{}
    |> Company.changeset(attrs)
    # Update somehow the `company_id` field from the associated row on users table
    # matching the `attrs["owner_id"]`
    |> Repo.insert()
  end

I am not sure if it’s possible to do this with schema associations or if I have to do the whole thing with Ecto.Multi.

Thank you for your time.