This is basically the TL;DR for my Diving into Ecto series. I always hate having to skim a long blog post looking for a quick answer, and I know you do too. With that in mind I'm going to make this post a list of common and not so common queries you can do with Ecto. I will keep it up-to-date as I discover more interesting queries and ways of using Ecto.
The repo with this post reproduced in the README can be found at https://github.com/parkerl/ecto_query_library. The queries below can be found in a usable form in lib/fishing_spot/queries.ex
. If you find something incorrect please open a pull request. I would like to make this a community resource rather than my personal toolbox.
For the full story behind this project and the queries start here Diving into Ecto: Part 1.
The query library is currently built using the following setup.
- Elixir 1.9
- Ecto_SQL 3.2
- Postgres 12
_Master includes broken tests it is up to you to make these tests pass. Use the examples in this readme to solve and goto the branch solutions
if you get stuck.
- Tests to update include
FishermanTest
,FishLandedTest
. Theses tests depend onContext.Fish
,Fisherman
, andFishLanded
. - You will have to update queries and changesets
- Start with FishermanTest
FishermanEctoChangesetsTests
as later solutions depend on earlier solutions. Then work on FishLandedTest and finally complete FishermanTestFishermanEctoQueryTests
.
- Select Styles
- Select Distinct
- Distinct on Expression
- Distinct with Order By
- Max
- Where
- Count
- Count Distinct
- Group By with Max
- Group By with Having
- Limit
- Limit and Offset
- Order By
- Joins
- Joining with a Fragment
- Max in two queries
- Record with Max Value via Self Join
- Record with Max Value via Subquery
- Record with Max Value via Join Fragment
- Keyword Where
- Keyword Where Referencing Another Model
- Where with In Clause
- Where with Not In Clause
- Complex Muti-join Multi-where
- Using a Select Fragment
- Complex Select Logic Using a Fragment
- Partial-preloading
- Binding Fun
- Working with Prefixes
Demonstrates how the various select styles change the return structure.
# Default: The entire model comes back as a list of structs.
Repo.all(
from fisherman in Fisherman
)
06:11:18.292 [debug] SELECT f0."id", f0."inserted_at", f0."updated_at", f0."name", f0."date_of_birth" FROM "fishermen" AS f0 [] OK query=0.5ms
[%FishingSpot.Fisherman{meta: #Ecto.Schema.Metadata<:loaded>,
date_of_birth: #Ecto.Date<1970-01-02>,
fish_landed: #Ecto.Association.NotLoaded<association :fish_landed is not loaded>,
fishermen_trips: #Ecto.Association.NotLoaded<association :fishermen_trips is not loaded>,
id: 1, inserted_at: #Ecto.DateTime<2015-09-29T12:05:05Z>, name: "Mark",
trips: #Ecto.Association.NotLoaded<association :trips is not loaded>,
updated_at: #Ecto.DateTime<2015-09-29T12:05:05Z>},
# The same as above. The entire model comes back as a list of structs.
Repo.all(
from fisherman in Fisherman,
select: fisherman
)
06:11:18.292 [debug] SELECT f0."id", f0."inserted_at", f0."updated_at", f0."name", f0."date_of_birth" FROM "fishermen" AS f0 [] OK query=0.5ms
[%FishingSpot.Fisherman{meta: #Ecto.Schema.Metadata<:loaded>,
date_of_birth: #Ecto.Date<1970-01-02>,
fish_landed: #Ecto.Association.NotLoaded<association :fish_landed is not loaded>,
fishermen_trips: #Ecto.Association.NotLoaded<association :fishermen_trips is not loaded>,
id: 1, inserted_at: #Ecto.DateTime<2015-09-29T12:05:05Z>, name: "Mark",
trips: #Ecto.Association.NotLoaded<association :trips is not loaded>,
updated_at: #Ecto.DateTime<2015-09-29T12:05:05Z>},
# Selects only the given fields. Returns a list of lists.
Repo.all(
from fisherman in Fisherman,
select: [fisherman.name, fisherman.date_of_birth]
)
06:11:18.305 [debug] SELECT f0."name", f0."date_of_birth" FROM "fishermen" AS f0 [] OK query=0.4ms
[["Mark", #Ecto.Date<1970-01-02>], ["Kirk", #Ecto.Date<1978-03-05>],
["Joe", #Ecto.Date<1973-10-15>], ["Lew", #Ecto.Date<1976-01-05>]]
# Selects only the given fields. Returns a list of tuples.
Repo.all(
from fisherman in Fisherman,
select: { fisherman.name, fisherman.date_of_birth }
)
06:11:18.306 [debug] SELECT f0."name", f0."date_of_birth" FROM "fishermen" AS f0 [] OK query=0.4ms
[{"Mark", #Ecto.Date<1970-01-02>}, {"Kirk", #Ecto.Date<1978-03-05>},
{"Joe", #Ecto.Date<1973-10-15>}, {"Lew", #Ecto.Date<1976-01-05>}]
# Selects only the given fields. Returns a list of maps with data in the given keys.
Repo.all(
from fisherman in Fisherman,
select: %{ fisherman_name: fisherman.name, fisherman_dob: fisherman.date_of_birth }
)
06:11:18.307 [debug] SELECT f0."name", f0."date_of_birth" FROM "fishermen" AS f0 [] OK query=0.3ms
[%{fisherman_dob: #Ecto.Date<1970-01-02>, fisherman_name: "Mark"},
%{fisherman_dob: #Ecto.Date<1978-03-05>, fisherman_name: "Kirk"},
%{fisherman_dob: #Ecto.Date<1973-10-15>, fisherman_name: "Joe"},
%{fisherman_dob: #Ecto.Date<1976-01-05>, fisherman_name: "Lew"}]
from fish in FishLanded,
select: fish.weight,
distinct: true
12:18:21.346 [debug] SELECT DISTINCT f0."weight" FROM "fish_landed" AS f0 [] OK query=9.5ms decode=3.0ms
from fish in FishLanded,
distinct: fish.weight
2:26:59.260 [debug] SELECT DISTINCT ON (f0."weight") f0."id", f0."inserted_at", f0."updated_at", f0."date_and_time", f0."weight", f0."length", f0."fisherman_id", f0."location_id", f0."fly_type_id", f0."fish_species_id" FROM "fish_landed" AS f0 [] OK query=3.3ms decode=0.1ms queue=0.1ms
[%FishingSpot.FishLanded{__meta__: #Ecto.Schema.Metadata<:loaded>,
date_and_time: #Ecto.DateTime<2012-06-12 21:51:26>,
fish_species: #Ecto.Association.NotLoaded<association :fish_species is not loaded>,
fish_species_id: 1,
fisherman: #Ecto.Association.NotLoaded<association :fisherman is not loaded>,
fisherman_id: 1,
fly_type: #Ecto.Association.NotLoaded<association :fly_type is not loaded>,
fly_type_id: 3, id: 886, inserted_at: #Ecto.DateTime<2016-02-18 04:51:26>,
length: #Decimal<6>,
location: #Ecto.Association.NotLoaded<association :location is not loaded>,
location_id: 1, updated_at: #Ecto.DateTime<2016-02-18 04:51:26>,
weight: #Decimal<1>},...
from fish in FishLanded,
distinct: fish.weight,
order_by: fish.date_and_time
12:37:15.371 [debug] SELECT DISTINCT ON (f0."weight") f0."id", f0."inserted_at", f0."updated_at", f0."date_and_time", f0."weight", f0."length", f0."fisherman_id", f0."location_id", f0."fly_type_id", f0."fish_species_id" FROM "fish_landed" AS f0
ORDER BY f0."weight", f0."date_and_time" [] OK query=4.9ms decode=0.2ms
[%FishingSpot.FishLanded{__meta__: #Ecto.Schema.Metadata<:loaded>,
date_and_time: #Ecto.DateTime<2012-06-06 21:51:25>,
fish_species: #Ecto.Association.NotLoaded<association :fish_species is not loaded>,
fish_species_id: 2,
fisherman: #Ecto.Association.NotLoaded<association :fisherman is not loaded>,
fisherman_id: 3,
fly_type: #Ecto.Association.NotLoaded<association :fly_type is not loaded>,
fly_type_id: 2, id: 571, inserted_at: #Ecto.DateTime<2016-02-18 04:51:25>,
length: #Decimal<1>,
location: #Ecto.Association.NotLoaded<association :location is not loaded>,
location_id: 2, updated_at: #Ecto.DateTime<2016-02-18 04:51:25>,
weight: #Decimal<1>}, ...
from fish in FishLanded,
select: max(fish.length)
from fish in FishLanded,
where: fish.length > 24
from fish in FishLanded,
select: count(fish.id),
where: fish.length > 24
from fish in FishLanded,
select: count(fish.fisherman_id, :distinct)
from fish in FishLanded,
join: fisherman in assoc(fish, :fisherman),
group_by: fisherman.name,
select: [max(fish.length), fisherman.name]
from fish in FishLanded,
join: fisherman in assoc(fish, :fisherman),
group_by: fisherman.name,
having: count(fish.id) > 15,
select: [max(fish.length), fisherman.name]
from fish in FishLanded,
limit: 10
Demonstrates the use of limit and offset using a calculated value. Also, demonstrates the use of Repo.aggregate/3
.
fish_count = Repo.aggregate(FishLanded, :count, :id) |> div(2)
Repo.all(
from fish in FishLanded,
limit: 10,
offset: ^fish_count
)
Demonstrates ordering ascending and descending including keyword syntax.
from fisherman in Fisherman,
order_by: fisherman.name,
select: fisherman.name
21:50:02.022 [debug] SELECT f0."name" FROM "fishermen" AS f0 ORDER BY f0."name" [] OK query=4.0ms
["Joe", "Kirk", "Lew", "Mark"]
from fisherman in Fisherman,
order_by: [desc: fisherman.name],
select: fisherman.name
21:50:02.025 [debug] SELECT f0."name" FROM "fishermen" AS f0 ORDER BY f0."name" DESC [] OK query=0.5ms
["Mark", "Lew", "Kirk", "Joe"]
from fisherman in Fisherman,
order_by: :name,
select: fisherman.name
21:50:02.022 [debug] SELECT f0."name" FROM "fishermen" AS f0 ORDER BY f0."name" [] OK query=4.0ms
["Joe", "Kirk", "Lew", "Mark"]
from fisherman in Fisherman,
order_by: [desc: :name],
select: fisherman.name
21:50:02.025 [debug] SELECT f0."name" FROM "fishermen" AS f0 ORDER BY f0."name" DESC [] OK query=0.5ms
["Mark", "Lew", "Kirk", "Joe"]
fields = [:name, :date_of_birth]
13:40:42.863 [debug] SELECT f0."name" FROM "fishermen" AS f0 ORDER BY f0."name", f0."date_of_birth" [] OK query=0.2ms
from fisherman in Fisherman,
order_by: ^fields,
select: fisherman.name
13:40:42.863 [debug] SELECT f0."name" FROM "fishermen" AS f0 ORDER BY f0."name", f0."date_of_birth" [] OK query=0.2ms
Demonstrates interpolating the result of one query into another.
[big_fish] = Repo.all(
from fish in FishLanded,
select: max(fish.length)
)
Repo.all(
from fish in FishLanded,
join: fisherman in assoc(fish, :fisherman),
where: fish.length == ^big_fish,
select: [fish.length, fisherman.name]
)
Demonstrates left joins, self joins, and conditions in joins. Calculates the record with a maximum value by "folding" onto the same table.
from fish in FishLanded,
left_join: bigger_fish in FishLanded, on: fish.length < bigger_fish.length,
join: fisherman in assoc(fish, :fisherman),
where: is_nil(bigger_fish.id),
select: [fish.length, fisherman.name]
Demonstrates subqueries in where clauses.
from fish in FishLanded,
join: fisherman in assoc(fish, :fisherman),
where: fragment(
"? IN (SELECT MAX(biggest_fish.length) FROM fish_landed biggest_fish)", fish.length
),
select: [fish.length, fisherman.name]
Demonstrates the use of fragment in joins.
from fish in FishLanded,
join: fisherman in assoc(fish, :fisherman),
join: big_fish in fragment(
"(SELECT MAX(biggest_fish.length) AS length FROM fish_landed biggest_fish)"
),
on: fish.length == big_fish.length,
select: [fish.length, fisherman.name]
Demonstrates the use of a keyword list for generating where clauses. Values are AND
d. Also, shows that variables will be interpolated.
{_, date} = Ecto.Date.cast("1976-01-05")
Repo.all(
from fisherman in Fisherman,
where: [name: "Lew", date_of_birth: ^date]
)
=> SELECT f0."id", f0."inserted_at", f0."updated_at", f0."name", f0."date_of_birth"
FROM "fishermen" AS f0
WHERE ((f0."name" = 'Lew')
AND (f0."date_of_birth" = $1))
[{1976, 1, 5}]
where(Fisherman, [name: "Lew", date_of_birth: ^date]) |> Repo.all
=> SELECT f0."id", f0."inserted_at", f0."updated_at", f0."name", f0."date_of_birth"
FROM "fishermen" AS f0
WHERE ((f0."name" = 'Lew')
AND (f0."date_of_birth" = $1))
[{1976, 1, 5}]
Demonstrates referencing another model in a keyword where clause. Also shows that no join condition is required by join
. It defaults to ON TRUE
.
join(Fisherman, :inner, [], fish_landed in FishLanded)
|> where([fisherman, fish_landed], [name: "Lew", date_of_birth: ^date, id: fish_landed.fisherman_id])
|> Repo.all
=> SELECT f0."id", f0."inserted_at", f0."updated_at", f0."name", f0."date_of_birth"
FROM "fishermen" AS f0
INNER JOIN "fish_landed" AS f1 ON TRUE
WHERE (((f0."name" = 'Lew')
AND (f0."date_of_birth" = $1))
AND (f0."id" = f1."fisherman_id"))
[{1976, 1, 5}]
from fish in FishLanded,
join: fisherman in assoc(fish, :fisherman),
where: fisherman.name in ["Mark", "Kirk"],
group_by: fisherman.name,
order_by: fisherman.name,
select: %{biggest_fish: max(fish.length), fisherman: fisherman.name}
Demonstrates the use of not
to negate an in clause.
from fish in FishLanded,
join: fisherman in assoc(fish, :fisherman),
where: fisherman.name not in ["Mark", "Kirk"],
group_by: fisherman.name,
order_by: fisherman.name,
select: %{biggest_fish: max(fish.length), fisherman: fisherman.name}
Demonstrates joins, sub-querying and using map syntax in the select.
Uses the date_add/3
function. Demonstrates how to accomplish a "between" where clause.
from fish in FishLanded,
join: fly_type in assoc(fish, :fly_type),
join: fish_species in assoc(fish, :fish_species),
join: fisherman in assoc(fish, :fisherman),
join: trip in assoc(fisherman, :trips),
join: locations in assoc(trip, :locations),
join: location_types in assoc(locations, :location_type),
where: fragment(
"? IN (SELECT MAX(biggest_fish.length) FROM fish_landed biggest_fish)", fish.length
),
where: fish.date_and_time >= trip.start_date,
where: fish.date_and_time <= date_add(trip.end_date, 1, "day"),
select: %{
length: fish.length,
date_caught: fish.date_and_time,
fish_type: fish_species.name,
fly: fly_type.name,
fisherman: fisherman.name,
trip_start: trip.start_date,
trip_end: trip.end_date,
location: locations.name,
location_type: location_types.name
}
Demonstrates how to use a named column from a fragment or a positional column from an aggregate function in grouping or ordering.
from fish in FishLanded,
group_by: fragment("date"),
order_by: fragment("2"),
select: %{
date: fragment("date_trunc('day', ?) AS date", field(fish, :date_and_time)),
fish_count: count(fish.id)
}
Demonstrates how to use multiple columns to calculate a value. Also orders descending using positional column selection.
from fish in FishLanded,
join: fisherman in assoc(fish, :fisherman),
order_by: [desc: fragment("1")],
select: %{
bool: fragment(
"((? = 'Kirk' OR ? = 'Mark') AND NOT ? < 10) AS crazy_select",
field(fisherman, :name),
field(fisherman, :name),
field(fish, :length)),
fisherman: fisherman.name
}
Demonstrates how to select only parts of a join model in a preload. Uses both map and list select syntax.
query = Fisherman
|> join(:inner, [fisherman], fish in assoc(fisherman, :fish_landed))
|> where([fisherman], fisherman.id == 1)
|> select([fisherman, fish], %{fisherman: fisherman, length: fish.length})
|> preload([fisherman, fish], [fish_landed: fish])
Repo.first(query).fisherman.fish_landed |> IO.inspect
query = Fisherman
|> join(:inner, [fisherman], fish in assoc(fisherman, :fish_landed))
|> where([fisherman], fisherman.id == 1)
|> select([fisherman, fish], %{fisherman: fisherman, length: fish.length})
|> preload([fisherman, fish], [fish_landed: fish])
fisherman = Repo.first(query) |> List.first
fisherman.fish_landed |> IO.inspect
Demonstrates that bindings are order and not name dependent. See http://www.glydergun.com/a-bit-about-bindings/.
FishLanded
|> join(:inner, [fish], fly_type in assoc(fish, :fly_type))
|> join(:inner, [fish, fly_type], fish_species in assoc(fish, :fish_species))
|> join(:inner, [fish, fly_type, fish_type], fisherman in assoc(fish, :fisherman))
|> join(:inner, [fish, fly_type, fish_type, fisherman], trip in assoc(fisherman, :trips))
|> join(:inner, [fish, fly_type, fish_type, fisherman, trip],
locations in assoc(trip, :locations))
|> join(:inner, [fish, fly_type, fish_type, fisherman, trip, location],
location_types in assoc(location, :location_type))
|> select([fish], count(fish.id))
FishLanded
|> join(:inner, [fish], fly_type in assoc(fish, :fly_type))
|> join(:inner, [fish], fish_species in assoc(fish, :fish_species))
|> join(:inner, [fish], fisherman in assoc(fish, :fisherman))
|> join(:inner, [fish, fly_type, fish_type, fisherman],
trip in assoc(fisherman, :trips))
|> join(:inner, [fish, fly_type, fish_type, fisherman, trip],
locations in assoc(trip, :locations))
|> join(:inner, [fish, fly_type, fish_type, fisherman, trip, location],
location_types in assoc(location, :location_type))
|> select([fish], count(fish.id))
FishLanded
|> join(:inner, [fish], fly_type in assoc(fish, :fly_type))
|> join(:inner, [nemo], fish_species in assoc(nemo, :fish_species))
|> join(:inner, [bait], fisherman in assoc(bait, :fisherman))
|> join(:inner, [foo, bar, baz, ahab], trip in assoc(ahab, :trips))
|> join(:inner, [foo, bar, baz, ahab, set_sail], locations in assoc(set_sail, :locations))
|> join(:inner, [x, y, z, a, b, c], location_types in assoc(c, :location_type))
|> select([whatever], count(whatever.id))
Demonstrates how to work with schemas other than "public" in Postgres.
# The migration
def change do
execute "CREATE SCHEMA users"
create table(:accounts, prefix: :users) do
add :identifier, :string
add :name, :string
timestamps
end
end
# Inserting data
Repo.insert(
Ecto.Model.put_meta(
%Account{ identifier: "[email protected]", name: "Lew" },
prefix: "users"
)
)
Repo.insert(
Ecto.Model.put_meta(
%Account{ identifier: "[email protected]", name: "Mark" },
prefix: "users"
)
)
Repo.insert(
Ecto.Model.put_meta(
%Account{ identifier: "[email protected]", name: "John" },
prefix: "users"
)
)
#Querying
query = from accounts in Account
Repo.all(%{query | prefix: "users"})