A Bit About Ecto Bindings

Names Come and Go but Position is Forever

Nearly all the macros in Ecto.Query take a binding as the second or third argument. In all the examples in the documentation these amount to a list of single variable letter names. These letters are then used in the following arguments to setup parts of a query.

I've always found bindings to be a bit mysterious. Why don't the names matter? How do they magically figure out what tables to match up to in a query?

The documentation at http://hexdocs.pm/ecto/Ecto.Query.html#from/2 has this to say:

def published(query) do
  from p in query, where: p.published_at != nil
end

Notice we have created a p variable to represent each item in the query. In case the given query has more than one from expression, each of them must be given in the order they were bound:

def published_multi(query) do
  from [p,o] in query,
  where: p.published_at != nil and o.published_at != nil
end

Note the variables p and o must be named however you find more convenient as they have no importance in the query sent to the database.

Ok. This tells us that the names are unimportant and that the order is important if the query has multiple froms. I find this a bit opaque.

Assuming "multiple from expressions" really translates to any operation that adds a table to a query then we have to build up a list of bindings for each model we join onto the initial from. Here is an example of a query with lots of joins:

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


#Ecto.Query<from f0 in FishingSpot.FishLanded, join: f1 in assoc(f0, :fly_type),
 join: f2 in assoc(f0, :fish_species), join: f3 in assoc(f0, :fisherman),
 join: t in assoc(f3, :trips), join: l0 in assoc(t, :locations),
 join: l1 in assoc(l0, :location_type), select: count(f0.id)>

SELECT count(f0."id") FROM "fish_landed" AS f0 INNER JOIN "fly_types" AS f1 ON f1."id" = f0."fly_type_id" INNER JOIN "fish_species" AS f2 ON f2."id" = f0."fish_species_id" INNER JOIN "fishermen" AS f3 ON f3."id" = f0."fisherman_id" INNER JOIN "fishermen_trips" AS f7 ON f7."fisherman_id" = f3."id" INNER JOIN "trips" AS t4 ON t4."id" = f7."trip_id" INNER JOIN "locations_trips" AS l8 ON l8."trip_id" = t4."id" INNER JOIN "locations" AS l5 ON l5."id" = l8."location_id" INNER JOIN "location_types" AS l6 ON l6."id" = l5."location_type_id"  

I have included the inspected version of the query as well as the actual SQL the query produces. As you can see, we have an ever growing list of bindings with each new join. But wait, the docs implied that only the position is important. In the first three joins of this query we are linking to the model that is in the first position, FishLanded. Do we need all the bindings all the time?

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

#Ecto.Query<from f0 in FishingSpot.FishLanded, join: f1 in assoc(f0, :fly_type),
 join: f2 in assoc(f0, :fish_species), join: f3 in assoc(f0, :fisherman),
 join: t in assoc(f3, :trips), join: l0 in assoc(t, :locations),
 join: l1 in assoc(l0, :location_type), select: count(f0.id)>

SELECT count(f0."id") FROM "fish_landed" AS f0 INNER JOIN "fly_types" AS f1 ON f1."id" = f0."fly_type_id" INNER JOIN "fish_species" AS f2 ON f2."id" = f0."fish_species_id" INNER JOIN "fishermen" AS f3 ON f3."id" = f0."fisherman_id" INNER JOIN "fishermen_trips" AS f7 ON f7."fisherman_id" = f3."id" INNER JOIN "trips" AS t4 ON t4."id" = f7."trip_id" INNER JOIN "locations_trips" AS l8 ON l8."trip_id" = t4."id" INNER JOIN "locations" AS l5 ON l5."id" = l8."location_id" INNER JOIN "location_types" AS l6 ON l6."id" = l5."location_type_id"  

No! We can leave out bindings for positions we are not using in that particular macro call. What about names? Can they truly be arbitrary?

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

#Ecto.Query<from f0 in FishingSpot.FishLanded, join: f1 in assoc(f0, :fly_type),
 join: f2 in assoc(f0, :fish_species), join: f3 in assoc(f0, :fisherman),
 join: t in assoc(f3, :trips), join: l0 in assoc(t, :locations),
 join: l1 in assoc(l0, :location_type), select: count(f0.id)>

SELECT count(f0."id") FROM "fish_landed" AS f0 INNER JOIN "fly_types" AS f1 ON f1."id" = f0."fly_type_id" INNER JOIN "fish_species" AS f2 ON f2."id" = f0."fish_species_id" INNER JOIN "fishermen" AS f3 ON f3."id" = f0."fisherman_id" INNER JOIN "fishermen_trips" AS f7 ON f7."fisherman_id" = f3."id" INNER JOIN "trips" AS t4 ON t4."id" = f7."trip_id" INNER JOIN "locations_trips" AS l8 ON l8."trip_id" = t4."id" INNER JOIN "locations" AS l5 ON l5."id" = l8."location_id" INNER JOIN "location_types" AS l6 ON l6."id" = l5."location_type_id"  

I strongly recommend you avoid writing queries this confusing. It illustrates the point that the binding names are only relevant within the macro call in which they are defined. There is no magic tie between names as the query is built. This allows for a great deal of flexibility, but can also be a source of confusion.

What if we accidentally add more bindings than there are sources in the query?

FishLanded  
            |> join(:inner, [fish, foo, bar, baz], fisherman in assoc(fish, :fisherman))
            |> join(:inner, [fish, fisherman],
                    trip in assoc(fisherman, :trips))
            |> select([fish], count(fish.id))

#Ecto.Query<from f0 in FishingSpot.FishLanded,
 join: f1 in assoc(f0, :fisherman), join: t in assoc(f1, :trips),
 select: count(f0.id)>

SELECT count(f0."id") FROM "fish_landed" AS f0 INNER JOIN "fishermen" AS f1 ON f1."id" = f0."fisherman_id" INNER JOIN "fishermen_trips" AS f3 ON f3."fisherman_id" = f1."id" INNER JOIN "trips" AS t2 ON t2."id" = f3."trip_id"  

Ecto simply ignores the extra bindings.

And if we forget one?

FishLanded  
        |> join(:inner, [fish, foo, bar, baz], fisherman in assoc(fish, :fisherman))
        |> join(:inner, [fish, fisherman],
                trip in assoc(fisherman, :trips))
        |> join(:inner, [fish, trip],
                locations in assoc(trip, :locations))
        |> select([fish], count(fish.id))

#Ecto.Query<from f0 in FishingSpot.FishLanded,
 join: f1 in assoc(f0, :fisherman), join: t in assoc(f1, :trips),
 join: l in assoc(f1, :locations), select: count(f0.id)>

** (Ecto.QueryError) lib/queries.exs:166: could not find association `locations` on model FishingSpot.Fisherman in query:

from f0 in FishingSpot.FishLanded,  
  join: f1 in assoc(f0, :fisherman),
  join: t in assoc(f1, :trips),
  join: l in assoc(f1, :locations),
  select: count(f0.id)

    (ecto) lib/ecto/repo/queryable.ex:91: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:15: Ecto.Repo.Queryable.all/4
           lib/queries.exs:170: FishingSpot.Queries.composed_biggest_fish_details/0

Things go badly, of course, because Ecto attempts to join on the wrong model.

A binding is a hook that Ecto can use within macro calls to determine which fields belong to which data source. It uses the variable names inside the macro call and the variable position between calls.

I encourage you to use your best judgement to write clean and clear Ecto queries. Personally I prefer the first version of the query above as it makes all the bindings and positions explicit all the way down.

I hope this takes some of the mystery out of bindings.

Acknowledgements

A huge thanks to Kim Barnes (@Kimberlydbarnes) and David Madouros (@dmadouros) for reviewing and updating the styling of this post.