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/ectoquerylibrary. 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. Some features are only avaiable on Ecto master. I will strive to indicate which queries will only run on master.

• Elixir 1.2
• Ecto master
• Postgres 9.4

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: not fisherman.name 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"})