Hey! My apologies if this question is a bit confusingly worded, I’m a beginner to phoenix and am picking up the lingo along the way.

I’m building a phoenix api project and I have been having a bit of trouble wrapping my head around how to implement building associations between 2 schemas for when you might not have previously inserted them in the past – but still have the flexibility available to me to add a child object into its own schema.

More concretely, I have the concept of a seller and a product. I’d like to be able to insert products on their own into my db, but I also want to be able to create a new seller with a new product and insert it into the db in one transaction. If the product actually happened to already exist when inserting a new merchant, I’d like to have that product then be associated with the merchant.

So, from a client, I was hoping that my inbound json payload would potentially be represented by the following structure.

Note, at this point, neither the seller or the product exists in the db yet:

{
	"seller": {
		"name": "seller1",
		"description": "the first seller",
		"product": {
			"name": "product1"
		}
	}
}

After this point, I would like to make it so I could proceed to do something where I can create a new seller but link it up with a potentially existing product that already exists in my products table:

{
	"seller": {
		"name": "seller2",
		"description": "the second seller",
		"product": {
			"name": "product1"
		}
	}
}

I’m actually struggling with the first step, because I’m not entirely sure how to insert both of those new entities at the same time and build out the association upon insert. Do I need to insert them one at a time? Or is it at all possible for me to do it all in one transaction? Or do I need to somehow access the child product json object, insert that into my products db, then after it has been inserted, proceed to insert the seller. After the seller is inserted, proceed to build the association? I feel like there should be a way to insert them both at the same time?

What I have so far:

My Seller Schema:

defmodule MyApp.Sellers.Seller do
  use Ecto.Schema
  import Ecto.Changeset
  @primary_key {:id, :binary_id, autogenerate: true}
  schema "sellers" do
    field :name, :string
    field :description, :string

    many_to_many :products, MyApp.Offerings.Product,
      join_through: "sellers_products",
      on_replace: :delete

    timestamps()
  end

  def changeset(seller, attrs) do
    seller
    |> cast(attrs, [
      :name,
      :description,
    ])
    |> cast_assoc(:products) #How do I create both a seller and a product at the same time from the nested json?
    |> validate_required([:name, :description])
  end
end

My Product Schema:

defmodule MyApp.Offerings.Product do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :binary_id, autogenerate: true}
  schema "products" do
    field :name, :string

    many_to_many :sellers, MyApp.Sellers.Seller,
      join_through: "sellers_products",
      on_replace: :delete

    timestamps()
  end

  def changeset(product, attrs) do
    product
    |> cast(attrs, [
      :name
    ])
    |> validate_required([
      :name
    ])
  end
end

My associated migrations:

defmodule MyApp.Repo.Migrations.CreateProducts do
  use Ecto.Migration

  def change do
    create table(:products, primary_key: false) do
      add :id, :uuid, primary_key: true
      add :name, :string, null: false
      timestamps()
    end

    create unique_index(:products, [:name]) #Will help guarantee uniqueness
  end
end

defmodule MyApp.Repo.Migrations.CreateSellers do
  use Ecto.Migration

  def change do
    create table(:sellers, primary_key: false) do
      add :id, :uuid, primary_key: true
      add :name, :string, null: false
      add :description, :text, null: false

      timestamps()
    end
  end
end

And the many_to_many table creation:

defmodule MyApp.Repo.Migrations.AssociateProductsToSellers do
  use Ecto.Migration

  def change do
    create table(:sellers_products, primary_key: false) do
      add :seller_id, references(:sellers, type: :uuid), primary_key: true
      add :product_id, references(:products, type: :uuid), primary_key: true
      timestamps()
    end

    create index(:sellers_products, [:seller_id])
    create index(:sellers_products, [:product_id])

    create unique_index(:sellers_products, [:seller_id, :product_id])
  end
end

And finally, my Sellers Context create function:

  def create_seller(attrs \\ %{}) do #Should I be inserting the product here first, then inserting a seller, then associating them??
    |> Seller.changeset(attrs)
    |> Repo.insert()
  end