Ecto Preload Nested Associations with joins

Recently I had an issue where I was loading some “models” in ecto, and I needed to preload some of the associations along with the parent record. I had it working with a very simple bit of code.

def get_franchises do
  Franchise
  |> preload(merchants: [stores: :shopify])
  |> Repo.all()
end

As you can see from this bit of code, some of the associations were nested, Franchise has many Merchants, Merchants have many stores, and a store can have a Shopify record. What I noticed when I started using the returned data was that some of the stores didn’t have a Shopify record. For my purposes, I wasn’t interested in the stores without a Shopify record, so I wanted to exclude them from my query results. In its original form, there were several queries being made to satisfy the ecto results. I struggled with how to include a where clause in the preload statement, until I came across something that led me to my desired result. Here’s the end game, a single SQL query resulting from an ecto statement with a preload that limits the stores to those with a Shopify record. You could further fine tune your query to return a subset of these results if you desired.

def get_franchises do
  Franchise
  |> join(:inner, [f], m in assoc(f, :merchants))
  |> join(:inner, [_, m], s in assoc(m, :stores))
  |> join(:inner, [_, _, s], sh in assoc(s, :shopify))
  |> preload([_, m, s, sh], merchants: {m, stores: {s, shopify: sh}})
  |> Repo.all()
end

This ecto logic results in a single query that uses the single query results to build the preload records instead of using additional queries for the associations, and because it uses inner joins, we are guaranteed to have the records we are looking for will no missing associations. For me the magic is being able to use the single query results for the preload values, so you could taylor the query so that it returns just what you are looking for.

• phoenix