Ecto - Using multiple foreign keys to setup a relationship

Phoenix Forum Questions / Help
,
1

Hi,

I have 4 modules, a Studio, a Studio Type, a Site, and a Config. The setup is that each site has many studios and configs, and so does each studio type. This means both studios and configs have a site ID and studio type ID:

Studio:

  • id
  • site_id
  • studio_type_id

Config:

  • id
  • site_id
  • studio_type_id

Site:

  • id

StudioType:

  • id

I’m trying to setup a has_many relationship between studios and configs, but I’m not sure how. I’ve tried:

defmodule Studio do
    has_many :configs, Config, foreign_key: [:site_id, :studio_type_id]
end

But this only accepts single entries. Is this setup even possible with ecto, or would I need a custom function in the Studio module to load associated configs? i’m keen to avoid changing the primary key of the studios table.

Thanks,
Mark

1 Like
2 
defmodule Studio do
  schema "studios" do
    # ...
    belongs_to :site, Site
    belongs_to :studio_type, StudioType
    has_many :configs,
      from: Config,
      foreign_key: [:site_id, :studio_type_id],
      on_replace: :delete
  end
end
1 Like
3

I just did the one site to many studios and one site to many configs since it wasn’t clear exactly how the 4th table fit in plus I didn’t want to still all the fun. This is very similar to Polymorphic associations with many to many — Ecto v3.9.4.

Schemas

defmodule ExampleApp.Wordpress.Site do
  use Ecto.Schema
## one to many
## one to another many

  schema "sites" do
    #...
    timestamps()
  end

  def changeset(struct, params \\ %{}) do
    struct
    |> Ecto.Changeset.cast(params, [])
  end

end

defmodule ExampleApp.Config do
  use Ecto.Schema

  ## many to one site
  ## many to many studios

  schema "configs" do
   many_to_many :sites, ExampleApp.Wordpress.Site,
      join_through: "config_sites"

    timestamps()
  end

  def changeset(struct, params \\ %{}) do
    struct
    |> Ecto.Changeset.cast(params, [:id])
  end

end

defmodule ExampleApp.Studio do
  use Ecto.Schema
  ## many to one site
  ## many to many configs

  schema "studios" do
       many_to_many :sites, ExampleApp.Wordpress.Site,
      join_through: "studio_sites"
    timestamps()
  end

  def changeset(struct, params \\ %{}) do
    struct
    |> Ecto.Changeset.cast(params, [:id])
  end

end

Migration

defmodule ExampleApp.Repo.Stuff do
  use Ecto.Migration

  def change do
    
    create table(:sites) do
      timestamps()
    end
    
    create table(:configs) do
      timestamps()
    end
    
    create table(:studios) do
      timestamps()
    end

create table("studio_sites") do
  add :site_id, references(:sites)
  add :studio_id, references(:studios)
end

create table("config_sites") do
  add :site_id, references(:sites)
  add :config_id, references(:configs)
end

end # end of change
end # end of module

Results

Here’s some output of the successful implementation:

postgres=# \c example_app_dev
You are now connected to database "example_app_dev" as user "postgres".
example_app_dev=# \dt
               List of relations
 Schema |       Name        | Type  |  Owner   
--------+-------------------+-------+----------
 public | config_sites      | table | postgres
 public | configs           | table | postgres
 public | schema_migrations | table | postgres
 public | sites             | table | postgres
 public | studio_sites      | table | postgres
 public | studios           | table | postgres
(6 rows)

example_app_dev=# \d configs
                                          Table "public.configs"
   Column    |              Type              | Collation | Nullable |               Default               
-------------+--------------------------------+-----------+----------+-------------------------------------
 id          | bigint                         |           | not null | nextval('configs_id_seq'::regclass)
 inserted_at | timestamp(0) without time zone |           | not null | 
 updated_at  | timestamp(0) without time zone |           | not null | 
Indexes:
    "configs_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "config_sites" CONSTRAINT "config_sites_config_id_fkey" FOREIGN KEY (config_id) REFERENCES configs(id)


example_app_dev=# \d config_sites
                             Table "public.config_sites"
  Column   |  Type  | Collation | Nullable |                 Default                  
-----------+--------+-----------+----------+------------------------------------------
 id        | bigint |           | not null | nextval('config_sites_id_seq'::regclass)
 site_id   | bigint |           |          | 
 config_id | bigint |           |          | 
Indexes:
    "config_sites_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "config_sites_config_id_fkey" FOREIGN KEY (config_id) REFERENCES configs(id)
    "config_sites_site_id_fkey" FOREIGN KEY (site_id) REFERENCES sites(id)
1 Like