This post is the first in a planned series on Ecto. Its aim is to explain Ecto to developers who are already familiar with Rails and ActiveRecord.
Ecto in a Phoenix app performs roughly the same function as ActiveRecord in a Ruby on Rails app. Both are used to validate data and to query and update the database. However, their design is quite different, and for Rails developers learning Phoenix, Ecto can take some getting used to.
The first post in this series will cover querying the database with Ecto. It's intended as a quick reference for Rails devs who know how to load records from a DB using ActiveRecord, and want to know how to get the same functionality with Ecto in Phoenix.
(PS: if you're a Rails developer learning Phoenix, you can learn much more from the course Phoenix on Rails, a comprehensive guide to Phoenix written for developers who already know Ruby on Rails.)
The Basics
In Rails you query the DB using the class methods of an ActiveRecord model:
Person.all
Person.find(1)
Person.where(last_name: "Smith")
In Phoenix there are no "models". Instead you define the shape of your data within a schema:
defmodule Person do
use Ecto.Schema
# The first arg to `schema/1` tells Ecto that the name of the
# DB table is 'people'. Note that we don't need to specify
# that 'people' has a primary key column called 'id' because
# `schema/1` assumes this by default.
schema "people" do
field :first_name, :string
field :last_name, :string
field :age, :integer
end
end
Then you query the DB using functions of the Repo module in conjunction with the schema and, for more complex queries, the Ecto.Query module.
Repo.all(Person)
Repo.get(Person, 1)
import Ecto.Query
Repo.all(from p in Person, where: last_name == "Smith")
# Alternative syntax:
Person
|> where(last_name: "Smith")
|> Repo.all
Finding 1 record
The most basic way to retrieve a record is by its primary key.
# ActiveRecord
Person.find(1)
Person.find raises ActiveRecord::RecordNotFound if no record exists with the given PK.
# Ecto
Repo.get(Person, 1)
Repo.get!(Person, 1)
Repo.get returns nil if no record is found; Repo.get! raises Ecto.NoResultsError.
To find a record by its other fields, use find_by in ActiveRecord and Repo.get_by in Ecto:
# ActiveRecord
Person.find_by(first_name: "Jimi", last_name: "Hendrix")
Person.find_by!(first_name: "Jimi", last_name: "Hendrix")
# Ecto
Repo.get_by(Person, first_name: "Jimi", last_name: "Hendrix")
Repo.get_by!(Person, first_name: "Jimi", last_name: "Hendrix")
find_by and Repo.get_by both return nil if no record is found. find_by! and Repo.get_by! will raise an error.
An important difference between ActiveRecord and Ecto is that in Ecto, Repo.get_by and Repo.get_by! will raise an error if they find more than one result. (Specifically, they raise an Ecto.MultipleResultsError.) ActiveRecord's find_by and find_by!, on the other hand, don't care and simply return the first result.
Finding N records
In ActiveRecord you can use where to return all results which match a query:
Person.where(last_name: "Smith")
In Ecto you construct a query using from then pass it to Repo.all:
import Ecto.Query
query = from p in Person,
where: p.first_name == "Jimi"
Repo.all(query)
Alternatively, instead of from you can use a functional syntax:
import Ecto.Query
Person
|> where([p], p.first_name == "Jimi")
|> Repo.all
(From here on all sample code assumes that Ecto.Query has already been imported.)
The choice of p in the above queries is arbitrary. We could have used any valid Elixir variable name:
Person
|> where([person], person.first_name == "Jimi")
|> Repo.all
For simpler queries you can pass a basic keyword list to where:
Person
|> where(first_name: "Jimi")
|> Repo.all
Existence
# ActiveRecord
Person.exists?(first_name: "Jimi")
# Ecto (declarative)
query = from p in Person, where: p.first_name == "Jimi"
Repo.exists?(query)
# Ecto (functional)
Person
|> where(first_name: "Jimi")
|> Repo.exists?
Comparison
# ActiveRecord
Person.where('age >= ?', 35)
# Ecto (declarative)
query = from p in Person,
where: p.age >= 35
Repo.all(query)
# Ecto (functional)
Person
|> where([p], p.age >= 35)
|> Repo.all
Inclusion
# ActiveRecord
Person.where(last_name: ["Hendrix", "Clapton"])
# Ecto (declarative)
query = from p in Person,
where: p.last_name in ["Hendrix", "Clapton"]
Repo.all(query)
# Ecto (functional)
Person
|> where([p], p.last_name in ["Hendrix", "Clapton"])
|> Repo.all
Negation
# ActiveRecord
Person.where.not(last_name: "Smith")
# Ecto (declarative)
query = from p in Person,
where: p.last_name != "Smith"
Repo.all(query)
# Ecto (functional)
Person
|> where([p], p.last_name != "Smith")
|> Repo.all
Use Elixir's not keyword to negate more complex queries:
# ActiveRecord
Person.where.not(last_name: ["Hendrix", "Clapton"])
# Ecto (declarative)
query = from p in Person,
where: p.last_name not in ["Hendrix", "Clapton"]
Repo.all(query)
OR
# ActiveRecord
Person.where(last_name: "Hendrix")
.or(Person.where(last_name: "Clapton"))
# Ecto (declarative)
query = from p in Person,
where: [last_name: "Hendrix"],
or_where: [last_name: "Clapton"]
Repo.all(query)
# Ecto (functional)
Person
|> where(last_name: "Hendrix")
|> or_where(last_name: "Clapton")
|> Repo.all
Ordering
# ActiveRecord
Person.order(:age) # ASC by default
Person.order(age: :desc)
# Ecto (declarative)
query = from p in Person,
order_by: p.age # or just order_by: :age
Repo.all(query)
query = from p in Person,
order_by: [desc: p.age] # or order_by: [desc: :age]
Repo.all(query)
# Ecto (functional)
Person
|> order_by(:age)
|> Repo.all
Person
|> order_by(desc: :age)
|> Repo.all
Note that Ecto uses the wording order_by instead of order, which can trip up ActiveRecord devs.
Also note the reversed ordering: in ActiveRecord you write age: :desc but in Ecto you write desc: :age. This is possible in Elixir because Elixir keyword lists can have duplicate keys; so for example it's possible to write desc: multiple times in an order_by query:
Person
|> order_by(desc: :age, desc: :first_name)
|> Repo.all
This wouldn't make sense in Ruby because the second value of the :desc key would overwrite the first value.
Selecting columns
# ActiveRecord
Person.select(:first_name)
query = from p in Person, select: [:first_name]
Repo.all(query)
Person
|> select([:first_name])
|> Repo.all
This returns a list of %Persons where only the first_name attribute is initialized (and the rest are nil.)
Limits
For more complex queries which you expect to return only one result, you can use Repo.one or Repo.one!:
import Ecto.Query
query = from p in Person,
where: p.age < 18
Repo.one(query) # nil if no results
Repo.one!(query) # error if no results
This searches for people whose age is below 18. It expects to find and return one result - if it finds more than one, it will raise an Ecto.MultipleResultsError. If it finds no results, it returns nil.
To return the first N results regardless of how many exist:
# ActiveRecord
Person.limit(10)
# Ecto (declarative)
query = from p in Person, limit: 10,
Repo.all(query)
# Ecto (functional)
Person
|> limit(10)
|> Repo.all
Use limit in combination with offset to get pagination:
# ActiveRecord
Person.order(:last_name).limit(20).offset(80)
# Ecto (declarative)
query = from p in Person,
order_by: :last_name,
limit: 20,
offset: 80
Repo.all(query)
# Ecto (functional)
Person
|> order_by(:last_name)
|> limit(20)
|> offset(80)
|> Repo.all
To return only the first or last from a set of results:
# ActiveRecord
Person.order(:last_name).first
Person.order(:last_name).last
# Ecto
Person
|> order_by(:last_name)
|> first
|> Repo.one
Person
|> order_by(:last_name)
|> last
|> Repo.one
Aggregates
To count all records:
# ActiveRecord
Person.count
# Ecto
Repo.aggregate(Person, :count)
Suppose we have two people with first name "John" and one with first name "Jack" in our DB. Then:
# ActiveRecord
Person.group(:first_name).count
#=> { "Jack" => 1, "John" => 2 }
# Ecto
Person
|> group_by(:first_name)
|> select([p], [p.first_name, count(p.id)])
#=> [["Jack", 1], ["John", 2]]
Uniqueness
Suppose again that our DB contains two people called "John" and one caled "Jack". Then:
# ActiveRecord
Person.distinct.pluck(:title)
#=> ["Jack", "John"]
# Ecto (declarative)
query = from p in Person, select: p.first_name, distinct: true
Repo.all(query)
#=> ["Jack", "John"]
# Ecto (functional)
Person
|> select([p], p.first_name)
|> distinct(true)
|> Repo.all
#=> ["Jack", "John"]
To count the number of distinct items:
# ActiveRecord
Person.distinct.count(:first_name)
#=> 2
# Ecto (declarative)
query = from p in Person, select: p.first_name, distinct: true
Repo.aggregate(query, :count)
#=> ["Jack", "John"]
# Ecto (functional)
person
|> select([p], p.first_name)
|> distinct(true)
|> repo.aggregate(:count)
#=> ["Jack", "John"]
Top comments (1)
Useful post, some improvements you can make:
you can shorten the first/last example to
hexdocs.pm/ecto/Ecto.Query.html#fi...
To get the same result when grouping you need to select into a tuple and pass the data to Map.new():
For counting distinct values do:
and I think
hexdocs.pm/ecto/Ecto.Query.API.htm...