matelau/ecto_query_library

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.

Versions

The query library is currently built using the following setup.

• Elixir 1.9
• Ecto_SQL 3.2
• Postgres 12

Test Instructions

_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 on Context.Fish, Fisherman, and FishLanded.
• 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 FishermanTest FishermanEctoQueryTests.

Table of Contents

• 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

---

Simple Queries with Different Select Styles

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"}]

Select Distinct

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

Distinct On Expression

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>},...

Distinct with Order By

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>}, ...

Max

from fish in FishLanded,
select: max(fish.length)

Simple Where

from fish in FishLanded,
where: fish.length > 24

Count

from fish in FishLanded,
select: count(fish.id),
where: fish.length > 24

Count Distinct

from fish in FishLanded,
select: count(fish.fisherman_id, :distinct)

Group By with Max

from fish in FishLanded,
join: fisherman in assoc(fish, :fisherman),
group_by: fisherman.name,
select: [max(fish.length), fisherman.name]

Group By with Having

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]

Limit

from fish in FishLanded,
limit: 10

Limit and Offset

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
)

Order By

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

Record with Max Value in Two Steps

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

Record with Max Value via Self Join

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]

Record with Max Value via Subquery

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]

Record with Max Value via Join Fragment

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]

Keyword Where

Demonstrates the use of a keyword list for generating where clauses. Values are ANDd. 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}]

Keyword Where Referencing Another Model

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

Where with In Clause

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}

Where with Not In Clause

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}

Complex Muti-join Multi-where

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
  }

Using a Select Fragment

with Named Grouping and Positional Ordering

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

Complex Select Logic Using a Fragment

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
}

Partial-preloading

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

Binding Fun

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

Working with Prefixes

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: "lew@example.com",  name: "Lew"  },
      prefix: "users"
      )
    )
    Repo.insert(
      Ecto.Model.put_meta(
       %Account{ identifier: "mark@example.com", name: "Mark" },
       prefix: "users"
      )
    )
    Repo.insert(
     Ecto.Model.put_meta(
       %Account{ identifier: "john@example.com", name: "John" },
       prefix: "users"
     )
    )

#Querying
    query = from accounts in Account
    Repo.all(%{query | prefix: "users"})