Ecto Associations and More Queries

This week we've started modeling our data with Ecto. Today we'll look at how we can model associated data and query for our data a bit more fully. Let's get started.

Project

So far we just have users. We ultimately want categories and threads and posts. Let's create them. First, we'll make a migration:

cd apps/firestorm_data
mix ecto.gen.migration create_categories_and_threads_and_posts

defmodule FirestormData.Repo.Migrations.CreateCategoriesAndThreadsAndPosts do
  use Ecto.Migration

  def change do
    # categories just have a title for now
    create table(:categories) do
      add :title, :string

      timestamps()
    end

    # threads belong to a category and have a title
    create table(:threads) do
      add :category_id, references(:categories)
      add :title, :string

      timestamps()
    end
    # We also add an index so we can find threads for a given category trivially
    create index(:threads, [:category_id])

    # posts belong to a thread and a user, and have a body
    create table(:posts) do
      add :thread_id, references(:threads)
      add :body, :text
      add :user_id, references(:users)

      timestamps()
    end
    # And we want to index posts by thread and user
    create index(:posts, [:thread_id])
    create index(:posts, [:user_id])
  end
end

We can run the migrations:

mix ecto.migrate

Now we have all of the database tables. Let's add the schemas, starting with tests. First, we'll add the category schema:

vim test/firestorm_data/schema/category_test.exs

defmodule FirestormData.CategoryTest do
  alias FirestormData.{Category, Repo}
  use ExUnit.Case

  setup do
    :ok = Ecto.Adapters.SQL.Sandbox.checkout(Repo)
  end

  test "creating a category" do
    elixir_changeset =
      %Category{}
      |> Category.changeset(%{title: "Elixir"})

    assert {:ok, _} = Repo.insert elixir_changeset
  end
end

If we run the test, it fails because there's no such module. We'll create it.

vim lib/firestorm_data/schema/category.ex

defmodule FirestormData.Category do
  use Ecto.Schema
  import Ecto.Changeset

  schema "categories" do
    field :title, :string

    timestamps()
  end

  def changeset(category, params \\ %{}) do
    category
    |> cast(params, [:title])
  end
end

Now we can create a category. Run the test to confirm. We'll do the same for threads:

vim test/firestorm_data/schema/thread_test.exs

This time we'll start by creating a category in the setup, then pattern matching it out of our metadata in the test when we need it.

defmodule FirestormData.ThreadTest do
  alias FirestormData.{Category, Thread, Repo}
  use ExUnit.Case

  setup do
    :ok = Ecto.Adapters.SQL.Sandbox.checkout(Repo)
    {:ok, category} = %Category{title: "Elixir"} |> Repo.insert
    {:ok, category: category}
  end

  test "creating a thread", %{category: category} do
    otp_changeset =
      %Thread{}
      |> Thread.changeset(%{category_id: category.id, title: "OTP is neat"})

    assert {:ok, _} = Repo.insert otp_changeset
  end
end

We know we need to create the Thread schema. Let's do this, and we'll see how to handle associations as we go:

defmodule FirestormData.Thread do
  use Ecto.Schema
  import Ecto.Changeset

  schema "threads" do
    field :title, :string
    # We use belongs_to to specify that this thread has a parent category
    belongs_to :category, FirestormData.Category

    timestamps()
  end

  def changeset(thread, params \\ %{}) do
    thread
    |> cast(params, [:category_id, :title])
  end
end

This is enough to get the test passing. Let's confirm. Finally, we want to create the Post schema:

vim test/firestorm_data/schema/post_test.exs

This time there's a bit more setup. We need to make a Category , a Thread , and a User . We'll do that, then generate our Post .

defmodule FirestormData.PostTest do
  alias FirestormData.{Category, User, Thread, Post, Repo}
  use ExUnit.Case

  setup do
    :ok = Ecto.Adapters.SQL.Sandbox.checkout(Repo)
    {:ok, category} = %Category{title: "Elixir"} |> Repo.insert
    {:ok, otp} = %Thread{title: "OTP is neat", category_id: category.id} |> Repo.insert
    {:ok, josh} = %User{username: "josh", email: "josh@smoothterminal.com", name: "Josh Adams"} |> Repo.insert
    {:ok, category: category, otp: otp, josh: josh}
  end

  test "creating a post", %{otp: otp, josh: josh} do
    post_changeset =
      %Post{}
      |> Post.changeset(%{thread_id: otp.id, body: "I know, right?", user_id: josh.id})

    assert {:ok, _} = Repo.insert post_changeset
  end
end

And we'll make the corresponding schema:

defmodule FirestormData.Post do
  use Ecto.Schema
  import Ecto.Changeset

  schema "posts" do
    field :body, :string
    belongs_to :thread, FirestormData.Thread
    belongs_to :user, FirestormData.User

    timestamps()
  end

  def changeset(post, params \\ %{}) do
    post
    |> cast(params, [:body, :thread_id, :user_id])
  end
end

Now our test passes. Let's talk about queries. First, what if we want to find a post by a given user? Let's add a test. We'll use a describe block to create some posts for every test inside of it, so we don't have to keep creating posts in each test to find.

defmodule FirestormData.PostTest do
  alias FirestormData.{Category, User, Thread, Post, Repo}
  use ExUnit.Case

  setup do
    :ok = Ecto.Adapters.SQL.Sandbox.checkout(Repo)
    {:ok, category} = %Category{title: "Elixir"} |> Repo.insert
    {:ok, otp} = %Thread{title: "OTP is neat", category_id: category.id} |> Repo.insert
    {:ok, josh} = %User{username: "josh", email: "josh@smoothterminal.com", name: "Josh Adams"} |> Repo.insert
    {:ok, category: category, otp: otp, josh: josh}
  end

  test "creating a post", %{otp: otp, josh: josh} do
    post_changeset =
      %Post{}
      |> Post.changeset(%{thread_id: otp.id, body: "I know, right?", user_id: josh.id})

    assert {:ok, _} = Repo.insert post_changeset
  end

  describe "given some posts" do
    setup [:create_other_users, :create_sample_posts]

    test "finding a post by a user", %{post1: post1, josh: josh} do
      # We need to fill this out
      assert true
    end
  end

  defp create_other_users(_) do
    adam =
      %User{username: "adam", email: "adam@smoothterminal.com", name: "Adam Dill"}
      |> Repo.insert!

    {:ok, adam: adam}
  end

  defp create_sample_posts(%{otp: otp, josh: josh, adam: adam}) do
    post1 =
      %Post{}
      |> Post.changeset(%{thread_id: otp.id, user_id: josh.id, body: "a"})
      |> Repo.insert!

    post2 =
      %Post{}
      |> Post.changeset(%{thread_id: otp.id, user_id: adam.id, body: "b"})
      |> Repo.insert!

    {:ok, post1: post1, post2: post2}
  end
end

Now we have a couple of posts on this thread and a couple of users. We'll find posts by Josh using normal queries. First, we'll import Ecto.Query in the test file:

defmodule FirestormData.PostTest do
  import Ecto.Query
  # ...
end

Now we'll build our query. This uses a custom DSL that we imported that begins with the from macro. We have to pin any variables we bring in to avoid ambiguity - you'll see that when we specify the user id:

  describe "given some posts" do
    setup [:create_other_users, :create_sample_posts]

    test "finding a post by a user", %{post1: post1, josh: josh} do
      query =
        from p in Post,
          where: p.user_id == ^josh.id

      posts = Repo.all query
      assert post1 in posts
    end
  end

Here we've found every post with my user id, and we've asserted that post1 is in there. Let's assert that the user for post1 is me:

  describe "given some posts" do
    setup [:create_other_users, :create_sample_posts]

    test "finding a post by a user", %{post1: post1, josh: josh} do
      query =
        from p in Post,
          where: p.user_id == ^josh.id

      posts = Repo.all query
      assert post1 in posts
      assert hd(posts).user.username == "josh"
    end
  end

If we run that, it will fail. That's because Ecto hasn't preloaded the relationship. We can tell Ecto to fill that in for us with Repo.preload . You can either handle this in the query or preload it from the database after the fact. We'll do it in the query:

  describe "given some posts" do
    setup [:create_other_users, :create_sample_posts]

    test "finding a post by a user", %{post1: post1, josh: josh} do
      query =
        from p in Post,
          where: p.user_id == ^josh.id,
          preload: [:user]

      posts = Repo.all query
      assert post1 in posts
      assert hd(posts).user.username == "josh"
    end
  end

Now our first test fails. That's because we're asserting that the data structure we use to represent post1 is in the data returned - it's not anymore, because the data returned has the user filled out. Let's modify that test and move on:

  describe "given some posts" do
    setup [:create_other_users, :create_sample_posts]

    test "finding a post by a user", %{post1: post1, josh: josh} do
      query =
        from p in Post,
          where: p.user_id == ^josh.id,
          preload: [:user]

      posts = Repo.all query
      assert post1.id in Enum.map(posts, &(&1.id))
      assert hd(posts).user.username == "josh"
    end
  end

Now we've preloaded the user onto our post. What if you have the post and you want to fetch the user onto it after the fact? This is dangerous because this is the path to N+1 queries, but it's worth showing off regardless:

  describe "given some posts" do
    setup [:create_other_users, :create_sample_posts]

    test "finding a post by a user", %{post1: post1, josh: josh} do
      query =
        from p in Post,
          where: p.user_id == ^josh.id

      posts = Repo.all query
      assert post1.id in Enum.map(posts, &(&1.id))
      post1 = post1 |> Repo.preload([:user])
      assert post1.user.username == "josh"
    end
  end

You can also perform aggregate queries, like counting the number of Post s in a Thread :

  describe "given some posts" do
    setup [:create_other_users, :create_sample_posts]
    # ...
    test "counting the posts in a thread", %{otp: otp} do
      query =
        from p in Post,
          where: p.thread_id == ^otp.id

      posts_count = Repo.aggregate(query, :count, :id)
      assert posts_count == 2
    end
  end

There's still a lot more to learn about queries that we can't cover in a short time, but I've linked to the Ecto docs and you can learn a lot from there. We'll cover more as we build out Firestorm further.

Summary

In today's episode we saw how to handle belongs_to associations. There's more to learn about associations, and you can play with those in tomorrow's exercise. See you soon!

Resources

• dailydrip/firestorm tagged before this episode
• dailydrip/firestorm tagged after this episode
• Ecto.Query docs