Elixir Ecto: How to store meta information about a many-to-many relationship

Question

Let's say I have the models User and Organization and a many-to-many relationship between the two. I now want to add information about the kind of relationship between the two. For example timestamps and position. How do I go about it and how do I query for this information?

I think the right direction is using a join schema. Following the Ecto docs that would be something like:

defmodule UserOrganization do
  use Ecto.Schema

  @primary_key false
  schema "users_organizations" do
    field :position, :string # adding information on the position of the user within the organization
    belongs_to :user, User
    belongs_to :organization, Organization
    timestamps # Added bonus, a join schema will also allow you to set timestamps
  end

  def changeset(struct, params \\ %{}) do
    struct
    |> Ecto.Changeset.cast(params, [:user_id, :organization_id])
    |> Ecto.Changeset.validate_required([:user_id, :organization_id])
    # Maybe do some counter caching here!
  end
end

defmodule User do
  use Ecto.Schema

  schema "users" do
    many_to_many :organizations, Organization, join_through: UserOrganization
  end
end

defmodule Organization do
  use Ecto.Schema

  schema "organizations" do
    many_to_many :users, User, join_through: UserOrganization
  end
end

# Then to create the association, pass in the ID's of an existing
# User and Organization to UserOrganization.changeset
changeset = UserOrganization.changeset(%UserOrganization{}, %{user_id: id, organization_id: id})

case Repo.insert(changeset) do
  {:ok, assoc} -> # Assoc was created!
  {:error, changeset} -> # Handle the error
end

Now how would I set the position when creating a record and how would I query the timestamps and the position?

Answer 1

If you want to retrieve the timestamps and the position of your composite schema you can add a has_many relationships on both User and Organization, like this

defmodule Organization do
  use Ecto.Schema

  schema "organizations" do
    many_to_many :users, User, join_through: UserOrganization
    has_many :user_organizations, UserOrganization
  end
end

Then you can Repo.preload the relationship user_organizations when you retrieve Organization and all the attributes of UserOrganization will be available to you.

Regarding how to set the position you can just add it to the changeset and make sure you allow the parameter on UserOrganization schema

 def changeset(struct, params \\ %{}) do
    struct
    |> Ecto.Changeset.cast(params, [:user_id, :organization_id, :position])
    |> Ecto.Changeset.validate_required([:user_id, :organization_id])
    # Maybe do some counter caching here!
  end

changeset = UserOrganization.changeset(%UserOrganization{}, %{user_id: id, organization_id: id, position: position})

And it should work all nicely for you.