I’ve spent several days trying to make my this work, connecting my tables using Ecto Association Guide – Ecto v2.2.11

I think Many-to-many associations in phoenix and ecto - #5 by marick might work.

But now my embedded schema is screwing things up. It is not part of the DB, and it only used for the UI, but I think I still need it.

I thought embedded schemas were not supposed to effect the DB? Is there an way around this?

# this works
org = %{name: "Toys R Us", slug: "toys-r-us"}
org_c = Organization.changeset(%Organization{},  org)
org_insert =  Repo.insert!(org_c) |> Repo.preload(:employees)  

# this does not - error is due to the .preload(:organizations)
emp = %{last_name: "schmo", first_name: "joe", role: "owner", email: "joe@schmo.com", password: "password"}
emp_c = Employee.registration_changeset(%Employee{}, emp)
emp_insert = Repo.insert!(emp_c) |> TurnStile.Repo.preload(:organizations)
# embedded schema doesn't exist in DB
** (Postgrex.Error) ERROR 42703 (undefined_column) column o0.owner_employee does not exist

# schema with embeds
schema "organizations" do
    field :email, :string
    field :name, :string
    field :slug, :string
    field :phone, :string
    # org has many employees; employees can belong to many organiztions (mostly for owners)
    many_to_many :employees, Employee, join_through: "organization_employees"
 

    embeds_one :owner_employee, Employee do
      field :first_name, :string
      field :last_name, :string
      field :_email, :string
      field :password, :string, virtual: true, redact: true
    end

schema "employees" do
    field :first_name, :string
    field :last_name, :string
    field :role, :string
    field :email, :string
    field :password, :string, virtual: true, redact: true
    field :confirmed_at, :naive_datetime
    # org has many employees within the company; employees belongs to many orgs
    many_to_many :organizations, Organization, join_through: "organization_employees"

# join table I want to use, but don't know how to use
defmodule Repo.Migrations.OrganizationEmployees do
  use Ecto.Migration

  def change do
    create table(:organization_employees) do
      add :organization_id, references(:organizations)
      add :employee_id, references(:employees)

      timestamps()
    end
    create unique_index(:organization_employees, [:organization_id, :employee_id])

  end
end