Thinking in ecto part 2

Queries and Relations

Now that we’ve created a basic query, you can perform a search on it using a primary key using Repo.get.

Repo is a genserver process started up in your supervisor tree that manages connections to postgresql. Using Repo methods allows you to send messages to the Repo process which performs the database call and performs the action on your behalf.

With a primary key, its easy to perform a basic query using Repo.get. Its pretty rare however to use get itself. In practice, you want to construct a queryable which you pass to repo. Queryables are special structs containing information for a query similar to Changesets.

An alternative way to create that get user above would be

import Ecto.Query
user = from(u in User)
  |> where([u], i.id == 1)
  |> Repo.one()

Repo.get is preferable for this simple case but the user of a full queryable is useful as we start to make ore sophisticated queries.

Repo Query Methods

I want to highlight a few of the Repo methods you will find yourself using on the regular. This is by no means an exhaustive list bt these are the ones I use regularly.

For the complete list of methods: https://hexdocs.pm/ecto/Ecto.Repo.html

user = Repo.get_by(User, email: "foo@example.com")

Gets you a single result where the the key as property matches the value given. If a matching record isn’t found, you’ll get back nil.

user = from(u in User)
  |> where([u], u.email == "foobar@example.com")
  |> Repo.one()

Repo.one takes a queryable and returns one matching record or nil. Only use this on queries where there is one and only one record at most or you will raise an error. I recommend only using it if you are matching on primary keys or unique indexes.

admins = ["admin@foo.com", "Peter@example.com"]
administrators = from(u in User)
  |> where([u], u.email in ^admins)
  |> Repo.all()

Repo.all takes a query and return a list of all matching records. In this case, we find all users where the email is one of a set of predefined emails stored in a list.

Relations

Most queries you perform are going to involve relationships between tables so suppose we want to add a list of todos to our user.

First we create a todo schema. We’ll cover a changeset for it after.

defmodule Myapp.Schema.User.Todo do
  use Ecto.Schema

  import Ecto
  import Ecto.Changeset
  import Ecto.Query
  alias Myapp.Schema.User

  schema "todos" do
    field :content, :string
    field :description, :string
    field :done, :boolean, default: false
    #field :owner, :id, references
    belongs_to :user, User, foreign_key: :owner_id
    timestamps()
  end

end

Notice I subset the schema definition under user. This is a common idiom in the new phoenix 1.3 to help scope models under owning ones to help codebases manage complexity. In this case, todos belong to users.

We set the belongs_to relationship with the belongs_to macro. It takes a symbol as the first attribute. this symbol is used to refer to the relationship to some other table. The second attribute is a defined schema such as the Schema for User.

I use the field owner to establish the referring key. by convention ecto expects you to use the name of the table + ‘_id’. For education’s sake, I’ve used an alternative to highlight the use of the foreign_key attribute to show how it can be overridden.

next we need to add the migration

defmodule Myapp.Web.Repo.Migrations.Myapp.Todo do
  use Ecto.Migration

  def change do
    create table(:todos) do
      add :content, :string
      add :description, :string
      add :done, :boolean, default: false, null: false
      add :owner_id, references(:users, on_delete: :delete_all), null: false

      timestamps()
    end
    create index(:todos, [:owner_id])
  end
end

Its important to add null: false here. Unlike rails, ecto relies on the database to enforce constraints. Like in the case with the uniqueness constraint, There is a changeset method we can use to convert the database error into user feedback.

Next thing we want to do is add a has_many constraint to the user schema.

defmodule Myapp.Schema.User do
  # ...

  schema "users" do
    # ...
    has_many :todos, Todo, foreign_key: :owner_id
    timestamps()
  end
end

Creating todos becomes slightly more complicated. Unlike Todos, You need to have an existing user in the database to perform an insertion. It makes sense in this case to add a user struct as the first argument.

defmodule TodoApi.Schema.User.Todo do
  # ...

  def create_changeset(%User{}=user, params \\ %{}) do
    build_assoc(user, :todos)
      |> cast(params, [:content, :description, :done])
      |> validate_required([:content])
      |> validate_length(:content, min: 1)
      |> unique_constraint(:text, name: :user_todo_text)
      |> assoc_constraint(:user)
  end

end

build_assoc and assoc_constraint are helper methods that serve to aid in creating a changeset that has the requires associations built. build_assoc takes a ecto struct and a symbol. It looks up the associated schema from that struct and finds the schema associated with the association via the symbol we pass in as the second argument. What we get back is an ecto struct for the associated model, (in this case a todo), with the user set as the user association. From here we pass it as the first argument to cast and use assoc_constraint for handling errors from the db if that user does not exist.

creating a todo now becomes easy provided we have an existing user

{:ok, todo} = Todo.create_changeset(user, %{
        content: "do the laundry"
      })
      |> Repo.insert()

Relational Queries

Now that We’ve covered some basic ground on relationships, lets revisit retrieving a user.

user = Repo.get(User, 1)

This user wont have any todos on them since ecto does not load associations by default. You’ll need to add it manually

user = case Repo.get(User, 1) do
  nil -> nil
  %User{}=user ->
    user |> Repo.preload(:todos)
end

This is suboptimal. We are retrieving the user and then hitting the database again. We can get a get everything in one go much more easily by using a preload in the query.

admins = ["admin@foo.com", "Peter@example.com"
user = from(u in User)
  |> where([u], e.email in admins)
  |> preload(:todos)
  |> Repo.all()