logo

Ecto Queries

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.

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)])


Icon Icon