Ecto many-to-many problem caused by embedded schema

Phoenix Forum Questions / Help
, ,
1

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
2

you misunderstood what embedded schemas are. they’re a way to describe a structured jsonb/map field.
if you don´t have a jsonb column named owner_employee you’d be better creating a virtual field(virutal fields are the ones that doesn’t exist in the db) that is a map.

for reference:
https://hexdocs.pm/ecto/embedded-schemas.html#migrations

3

Perhaps I do misunderstand. So these things don’t apply to me? It’s what I thought based on Embedded Schemas — Ecto v3.10.1

  • This data can live in memory, or can be stored in the database. (For me it’s memory)
  • You are maintaining intermediate-state data, like when UI form fields map onto multiple tables in a database. (I’m mapping to tables organizations, employees, and organization_employees)
  • If you wish to save your embedded schema to the database, you need to write a migration to include the embedded data. (I don’t wish to save it)

I don’t have owner_employee but I do have a join table that I’m trying, and failing, to use.

4

Embedded schemas without being saved to DB are basically a way to parse data by using Ecto constructs, more or less.

But if you start actively using them with other records that hit the DB you’ll have trouble, yeah. Either have them be virtual attributes (where they are used in other schemas) and/or just add them after a Repo.get and such.

5

so, there are a few ways to deal with that:

  • make the field in organizations a virtual one of the type map. being it a map, you can load whatever map you want there, even a employee struct loaded from the database.
  • if there is a query to define which row from employees is a owner_employee you can actually make it a relation… something like
schema "organizations" do
  ...
  has_one :owner_employee, Employee, through: "organization_employees", where: [is_owner: true]
end

and you can preload the owner_employee the same way you preload any other relation.

when you use a embedded schema this way inside another schema is always to describe a structured jsonb field.