Many_to_many relation, ecto and phoenix

Hello!

I am trying to create a many_to_many relation between two models in my app. But I cant seem to get it to work.
I want to have many special rules objects for each equipment model in my app, so I have these models:

defmodule App.Equipment do
  use App.Web, :model

  schema "equipment" do
    field :range, :integer
    field :name, :string
    field :strength, :integer
    
    many_to_many :special_rules, App.SpecialRules, join_through: App.SpecialRulesList

    timestamps()
  end
end

defmodule App.SpecialRule do
  use App.Web, :model

  schema "special_rules" do
    field :description, :string
    field :type, :integer
    field :name, :string

    many_to_many :equipment, App.Equipment, join_through: App.SpecialRulesList

    timestamps()
  end
end

defmodule App.SpecialRulesList do
  use App.Web, :model

  schema "special_rules_list" do
    belongs_to :special_rules, App.SpecialRule
    belongs_to :equipment, App.Equipment
    timestamps
  end
end

And these are my tables:

defmodule App.Repo.Migrations.CreateEquipment do
  use Ecto.Migration

  def change do
    create table(:equipment) do
      add :range, :integer
      add :name, :string
      add :strength, :integer
   end
end

defmodule App.Repo.Migrations.CreateSpecialRule do
  use Ecto.Migration

  def change do
    create table(:special_rules) do
      add :description, :text
      add :type, :integer
      add :name, :string
  end
end

defmodule App.Repo.Migrations.CreateEquipmentSpecialRuleJoin do
  use Ecto.Migration
  
  def change do
    create table(:special_rules_list, primary_key: false) do
      add :equipment_id, references(:equipment)
      add :special_rules_id, references(:special_rules)
      timestamps()
    end
end

When i try to load the relation with preload i the equipment controller i have

defmodule App.EquipmentController do
  use App.Web, :controller

  alias App.Equipment
  alias App.SpecialRule

  def index(conn, _params) do
    equipment = Repo.all(Equipment) |> Repo.preload([:special_rules])
    render(conn, "index.json", equipment: equipment)
end
end

But I always get some strange error, like (ERROR 42P01 (undefined_table): relation “special_rules_list” does not exist) does not exists or table does not exists (depending on how i load thing in the controller or present the data in the render method).

I run mix ecto.reset && mix phoenix.server whenever i do changes in the models or tables.

What am I doing wrong? Feels like there is some small detail I have missed.

What happens if you change…

add :special_rules_id, references(:special_rules)

to

add :special_rule_id, references(:special_rules)

? (singular instead of plural)

1 Like

If you have:

many_to_many :special_rules, App.SpecialRules, join_through: App.SpecialRulesList
This -> join_through: App.SpecialRulesList needs to have a module with the same name that will just map the table relationship.

In your case, you should change:

many_to_many :special_rules, App.SpecialRules, join_through: App.SpecialRulesList
to
many_to_many :special_rules, App.SpecialRules, join_through: "special_rules_list".

That might do the trick.

1 Like

I changed it to what you said, but i still get

ERROR 42P01 (undefined_table): relation “special_rules_list” does not exist

Changing the specials_rule_id to singular didnt make it either (can it be that sensitive to pluras and singulars?)

Ok i finally got it to work.

What I did was changing specials_rule_id to singular (special_rule_id). I also had the

defmodule App.Repo.Migrations.CreateEquipmentSpecialRuleJoin do
  use Ecto.Migration
  
  def change do
    create table(:special_rules_list, primary_key: false) do
      add :equipment_id, references(:equipment)
      add :special_rule_id, references(:special_rules)
    end
  end
end

In the same file as the App.Repo.Migrations.CreateEquipment. Now everything seems to work and I dont really know why.