Ecto many_to_many and joining through

This episode we'll add tags to our project and create more advanced associations. [Last lesson], we covered has_many and belongs_to associations and saw some of the conveniences Ecto offers once associations are defined.

This time, we'll move on to many_to_many associations and also set up some has_many through associations.

Many to many associations

In our app, links have a many-to-many relationship to users. Each user can tag many links. Each link can be tagged by many users. The neither links nor users have the other as a foreign key in its schema. Instead, the relationship is tracked through the join table "link_tags", which has both link_ids and user_ids. If user #n has bookmarked link #m, then there will be a bookmark record with a user_id of n and a link_id of m.

Similarly, links have a many-to-many relationship with tags and the "link_tags" join table also includes tag_id as foreign keys. If a given link has been tagged with a given tag, then there will be a link_tag record with the appropriate foreign keys for links and tags are set.

All we need to do to set up the many to many association in Ecto is to call the many_to_many/3 function in our schema definition.

The first argument is an atom that we want to use to access the associated records from in our schema. This is completely up to us to chose, but in general it's best to just use the name of the schema.

The second argument is the module name of the schema.

The third option is for arguments we can pass options to define how the tables are related to each other. As long as we use the standard names for foreign keys in our tables (e.g. call the foreign key for links "link_id" in the join table and call the foreign key for user "user_id", etc), we can just pass the module name of the schema for the join table via the join_through option as below:

defmodule Linkly.Link do
  use Ecto.Schema
  import Ecto.Changeset
  alias Linkly.{Bookmark, Link, LinkTag, Tag, User}

  schema "links" do
    field(:url)
    has_many(:bookmarks, Bookmark)
    has_many(:taggings, LinkTag)
    many_to_many(:tags, Tag, join_through: LinkTag)
    many_to_many(:users, User, join_through: LinkTag)

    timestamps()
  end
end

Now, in addition to being able to preload bookmarks onto a Tag schema, we can preload associated users and tags, too:

link = Repo.get!(Link, 3)
tag |> Repo.preload([:users, :tags])

Also note that using the join table to create a many-to-many relationship, doesn't prevent us from also creating a has_many relationship to the join table itself as we did above. It couldn't have been named link_tags, but I chose to call it "taggings".

For join tables with non-standard names for foreign keys, see the join_keys option in the Ecto docs.

Joining a has_many through another table

The many-to-many relationship from the User's side is virtually identical. Tags are associated as "tags" and links are associated as "tagged_links". Remember, we can pass in any name we link in the first argument:

defmodule Linkly.User do
  use Ecto.Schema
  alias Linkly.{Bookmark, Link, LinkTag, Tag}

  schema "users" do
    field(:about)
    field(:email)
    field(:username)
    has_many(:bookmarks, Bookmark)
    has_many(:bookmarked_links, through: [:bookmarks, :link])
    has_many(:taggings, LinkTag)
    many_to_many(:tagged_links, Link, join_through: LinkTag)
    many_to_many(:tags, Tag, join_through: LinkTag)


    timestamps()
  end
end

There is one interesting question, though. Users have two distinct relationships with links. They may have bookmarked a given link or they may have tagged it (or both). It's entirely common to bookmark a site without labeling it "interesting" or "read later" or somehow categorize it.

We could already access bookmarked links by doing a nested preload like bob = Repo.preload(bob, [bookmarks: [:link]]) and then accessing the link by looking at whats in the link attribute of each item in bob.bookmarks. We could even use Enum.filter to get a list of them.

If it's something we want to do often, though, it's a lot more convenient to let Ecto take care of it, by defining a has_many relationship. The only problem is that links don't have a user_id on them. We can give Ecto a path through multiple tables to create the join, by using the through option.

In the example above, we have

    has_many(:bookmarked_links, through: [:bookmarks, :link])

Here, the user schema specifies that it has many :bookmarks and the bookmark schema specifies that it belongs to :link, so we pass those directions as a list in through: [:bookmarks, :links]. If links had yet another relationship and we wanted users to have many of that schema, then we could just add a third atom to the list.

There are obvious performance concerns, but you can make the through path as long as you like.