Thinking in ecto part 2
Tue, Apr 25, 2017Queries 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()