Phoenix Ecto: foreign key not being inserted

Question

I am inserting a model A that contains a foreign key to another model B.

defmodule MyApp.ModelA do
  use MyApp.Web, :model

  schema "model_a" do
    field :type, :string, null: false
    field :data, :string, null: false
    belongs_to :model_b, MyApp.ModelB
    timestamps()
  end

  @required_fields ~w(type data)
  @optional_fields ~w()

  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, @required_fields, @optional_fields)
    |> assoc_constraint(:model_b)
  end
end

and the Model B:

defmodule MyApp.ModelB do
  use MyApp.Web, :model

  schema "model_b" do
    field :username, :string
    field :pass, :string
    has_many :model_a, MyApp.ModelA
    timestamps()
  end

  @required_fields ~w(username pass)
  @optional_fields ~w()

  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, @required_fields, @optional_fields)
    |> cast_assoc(:model_a)
    |> validate_required([])
  end
end

Model B exists, as I can get it via Repo.all(ModelB).

Model A changeset is successfully validated, and I can see the model_b_id value when I print model A changeset struct.

But when inserted, the reference is not inserted. Although I can see it when printing the changeset of model A, in the MySQL log, this field is completely missing, is not in the INSERT query.

I have played a little bit, and if I force this reference field to not be null in the MySQL table, then I get a {"does not exist", []} for this foreign key field when inserting as a Repo.insert(...) response, although the model B exists in database.

Stylite

Changeset validation does not check foreign key validation so you won't get a "does not exist" error before you actually try to insert. Can you post the schema of the models and the code you ran and the SQL logs? – Raiment

Soryry, maybe I didn't explain myself. I get the "does not exist" as a response of Repo.insert(...). Changeset validation indeed always works ok for this case. – Stylite

Are you absolutely sure you see model_b_id in the changeset? Your @optional_fields is empty according to the code you just posted. Can you try adding model_b_id to @optional_fields? – Raiment

Yes, absolutely sure I can see it if i print changeset once it has been created and validated. I see the id of the ModelB row in the model_b_id field of the changeset. Mind that this reference field in Model A was added in a migration, so it was not defined in the original table definition, but I don't think it has something to do. I created an extra new field and I was able to set a value to it. So it shouldn't has anything to do to have been defined in a migration. – Stylite

I have tried adding this model_b_id field in required_fields (and also in optional_fields in a second attempt) and I keep getting the same problem, but changeset validation does not complain, so it means that field value is actually set. The problem is when Repo.insert(..). – Stylite

Can you try to call changeset and Repo.insert from iex and post the whole output with the exact query that was sent from Ecto to the MySQL server? I can't think of any reason for Ecto to not send a field to the server. – Raiment

Answer 1

I was supremely interested in a response to this question.

I'd lost >7 hours to the exact condition of "I KNOW this row was created, WHY OH WHY is the changeset not letting me store the reference?"

I thought I'd leave a suggestion for anybody with the same frustration to check your database migration references.

I was sure I had triple checked them, but there it was, clear as day after a good nights sleep. I had setup references to wrong table.

Hopefully this saves somebody some time.

Peripeteia

damn you've actually saved me 7 more hours of sleep deprivation. THANK YOU!! – Fagen

Answer 2

I don't think Ecto pulls the ID field off the struct for you - I've been doing something like this:

defmodule MyApp.ModelA do
  use MyApp.Web, :model

  schema "model_a" do
    field :type, :string, null: false
    field :data, :string, null: false
    belongs_to :model_b, MyApp.ModelB
    timestamps()
  end

  @required_fields ~w(type data model_b_id)
  @optional_fields ~w()

  @doc """
  Method head to define what's coming.
  """
  def changeset(model, params \\ :empty)

  @doc """
  Catches the case where you're passing in a model_b struct instead of an integer.
  Just makes a recursive call with the correct type.
  """
  def changeset(model, %{model_b_id: nil, agency: %MyApp.ModelB{} = model_b} = params) do
    changeset(model, %{params | model_b_id: model_b.id})
  end

  @doc """
  I normally use assoc_constraint on the table index itself like so
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, @required_fields, @optional_fields)
    |> assoc_constraint(:model_b, name: :model_as_model_b_id_fkey)
  end
end

Hassan

Answer 3

What worked for me was the following:

model_b = Repo.get(MyApp.ModelB, 2)
model_a_changeset = Ecto.build_assoc(model_b, :model_as,
    type: "Model type",
    data: "Model data"
)
Repo.insert! model_a_changeset

(The number 2 is a model_b's id example, you have to figure out how to retrieve the correct parent id)

Sometimes you need to specify explicitly the foreign key on the change set as following:

model_a_changeset = Ecto.build_assoc(model_b, :model_as,
    type: "Model type",
    data: "Model data",
    model_b_id: model_b.id
)

Regress