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.
