Pantrypoints
Ecto Queries
February 15, 2022 2 minutes • 371 words
Table of contents
Repo
Repo has a limited number of queries.
Return a single record, and raise an error if more than one record is returned
Repo.one
Check for existence
Repo.exists?
Get the length of an model
length(@posts)
Get the count
Repo.one(from p in Post, select: count("*"))
Repo.aggregate(Post, :count, :id)
Get by something
Get a record by id
Pipe
User |> Repo.get(1)
User |> Repo.get!(1)
No Pipe
Repo.get(User, 1)
Repo.get!(User, 1)
Get first or last record
Pipe
User |> first |> Repo.one
User |> last |> Repo.one
No Pipe
last(User)
first(User)
Get a single record by a single attribute
Pipe
User |> Repo.get_by(first_name: "John")
User |> Repo.get_by!(first_name: "John")
Repo.get_by(User, first_name: "John")
Repo.get_by!(User, first_name: "John") # error if more than 1 record
Ecto Queries
This is needed for more complex queries that involve where select order_by etc.
- Keyword queries follow the pattern
fromwhereselect - Macro queries or Query Expresssions use the Schema name and pipes
These require:
import Ecto.Query
Otherwise you’ll get the common error:
cannot use ^variable_name outside of match clauses
Get multiple records by attribute and extract or “select” an attribute
Keyword Method Bindingless
query = from(User,
where: [first_name: "John"],
select: [:job])
Repo.all(query)
Keyword Method with Binding
This allows u to be called anywhere and have differet outputs determined by select:
query = from(u in User,
where: u.first_name == "John",
select: {u.job, u.age})
Repo.all(query)
Get last n records
Pipe
User
|> where([u], u.first_name != "John")
|> limit(5)
|> Repo.all
No Pipe
Repo.all(from u in User, where: u.first_name != "John", order_by: [desc: :inserted_at], limit: 5)
Negation
User
|> where([u], u.first_name != "John")
|> Repo.all
Multiple values
User
|> where([u], not (u.name in ["John", "Erin"]))
|> Repo.all
User
|> where([u], u.name in ["John", "Erin"])
|> Repo.all
Comparisons
User
|> where([u], u.score >= 100)
|> Repo.all
OR statements
User
|> where(first_name: "John")
|> or_where(first_name: "Jack")
|> Repo.all
Sorting
User
|> order_by(:first_name)
|> Repo.all
User
|> order_by(desc: :first_name)
|> Repo.all
User
|> order_by([:last_name, :first_name, desc: :age])
|> Repo.all
Pagination (Offset/Limit)
User
|> order_by(:first_name)
|> limit(5)
|> offset(10)
|> Repo.all
Loading
User
|> Repo.get(1)
|> Repo.preload([:addresses])
User
|> Repo.get(1)
|> Repo.preload([addresses: order(Address, :state)])
Grouping
User
|> group_by(:first_name)
|> select([:first_name])
|> Repo.all
Aggregates
User
|> Repo.aggregate(:count, :id)
User
|> Repo.aggregate(:count, :first_name)
User
|> group_by(:first_name)
|> select([u], [u.first_name, count(u.id)])