Many to many association preload error in Ecto

Question

I am learning Ecto and was trying out many to many relationship.
I am using MySQL DB,

schemas are like this

User Schema

  @primary_key {:id, :binary_id, autogenerate: true}
  schema "users" do
    field(:name, :string, required: true)
    field(:hashed_password, :string, required: true)
    field(:password, :string, virtual: true)
    timestamps()

    many_to_many(:methods, Bookie.Method.Model, join_through: "users_methods", on_replace: :delete)
  end

Methods Schema

  @primary_key {:id, :binary_id, autogenerate: true}
  schema "methods" do
    field(:function, :string, required: true)
    field(:method, :string, required: true)
    timestamps()

    many_to_many(:users, Bookie.User.Model, join_through: "users_methods", on_replace: :delete)
  end

the association of these 2 schema - users_methods


  schema "users_methods" do
    belongs_to(:users, Bookie.User.Model, type: :binary_id)
    belongs_to(:methods, Bookie.Method.Model, type: :binary_id)
  end

These are my migrations

  create table(:users, primary_key: false) do
      add(:id, :uuid, primary_key: true)
      add(:name, :string, null: false)
      add(:hashed_password, :string, null: false)
      timestamps()
    end

   create(unique_index(:users, [:name]))

    create table(:methods, primary_key: false) do
      add(:id, :uuid, primary_key: true)
      add(:function, :string, null: false)
      add(:method, :string, null: false)
      timestamps()
    end

 create table(:users_methods, primary_key: false) do
      add(:user_id, references("users", type: :uuid, on_delete: :delete_all))
      add(:method_id, references("methods", type: :uuid, on_delete: :delete_all))
    end

    create(index(:users_methods, [:method_id]))
    create(index(:users_methods, [:user_id]))

    create(
      unique_index(:users_methods, [:user_id, :method_id], name: :user_id_method_id_unique_index)
    )

This is my basic setup. Now as soon as i try to preload methods after querying/inserting user it gives me error.

  user_map = %{name: "User 1", password: "password"}

  changeset =
    User.changeset(%User{}, user_map)
    |> Ecto.Changeset.put_change(
      :hashed_password,
      User.hashed_password(changeset.changes[:password])
    )

  user =
    Repo.insert!(changeset)
    |> Repo.preload(:methods)

Error


  [debug] QUERY OK db=2.6ms queue=0.1ms
begin []
[debug] QUERY OK db=3.5ms
INSERT INTO `users` (`hashed_password`,`name`,`inserted_at`,`updated_at`,`id`) VALUES (?,?,?,?,?) ["$2b$12$6W3zoeCnfLRV3N0fzv5t..LFnZly93G4IvfM5DfQSaEWPLtK0jV8q", "User 1", {{2019, 6, 8}, {14, 29, 25, 458464}}, {{2019, 6, 8}, {14, 29, 25, 458477}}, <<208, 210, 137, 42, 54, 3, 75, 211, 148, 173, 9, 174, 171, 125, 44, 225>>]
[debug] QUERY OK db=0.7ms
commit []
[debug] QUERY ERROR source="methods" db=29.5ms
SELECT m0.`id`, m0.`function`, m0.`method`, m0.`inserted_at`, m0.`updated_at`, u1.`id` FROM `methods` AS m0 INNER JOIN `users` AS u1 ON u1.`id` IN (?) INNER JOIN `users_methods` AS u2 ON u2.`model_id` = u1.`id` WHERE (u2.`model_id` = m0.`id`) ORDER BY u1.`id` [<<208, 210, 137, 42, 54, 3, 75, 211, 148, 173, 9, 174, 171, 125, 44, 225>>]
** (Mariaex.Error) (1054): Unknown column 'u2.model_id' in 'where clause'
    (ecto) lib/ecto/adapters/sql.ex:450: Ecto.Adapters.SQL.sql_call!/6
    (ecto) lib/ecto/adapters/sql.ex:420: Ecto.Adapters.SQL.do_execute/6
    (ecto) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4
    (elixir) lib/enum.ex:1327: Enum."-map/2-lists^map/1-0-"/2

(Mariaex.Error) (1054): Unknown column 'u2.model_id' in 'where clause'

I am not getting above part :(

please someone help.

José Valim José Valim · Accepted Answer · 2019-06-08T17:23:43

The association foreign key is inflected from the last part of the schema name. Since all of your schemas are named Foo.Bar.Model, they all default to model_id. You can pass the join_keys option, to specify the relationships.

For example:

many_to_many(:methods, Bookie.Method.Model, join_through: "users_methods", on_replace: :delete)

should be:

many_to_many(:methods, Bookie.Method.Model, join_through: "users_methods", join_keys: [user_id: :id, method_id: :id], on_replace: :delete)

And:

many_to_many(:users, Bookie.User.Model, join_through: "users_methods", on_replace: :delete)

Should be:

many_to_many(:users, Bookie.User.Model, join_through: "users_methods", join_keys: [method_id: :id, user_id: :id], on_replace: :delete)

Although I would recommend following Ecto's naming convention, then everything will become much simpler.

Many to many association preload error in Ecto

1 Answers