TIL: Ecto query preload

Ihor Katkov
Nov 1 · 2 min read

I have such models:

> Order has many Deliveries, Delivery has many HistoryItems.

My goal was to get deliveries with its orders and history items in one query (for performance reasons). So ideal result should look like this

list({Delivery.t, Order.t, list(HistoryItem.t)})

At first, I wrote a query which returns deliveries with its orders.

 from d in Delivery,
join: o in Order, on: o.id == d.order_id,
where: … # Here my where clause
select: {d, o}

And it worked! I got a list of deliveries with orders. But when I decided to add history items, I was faced with a problem. If I add join on history items table, the result was very different from what I expected. It returned the same deliveries for each history item!

Query:

 from d in Delivery,
join: in Order, on: o.id == d.order_id,
join: h in HistoryItem, on: h.delivery_id == d.id,
where: … # Here my where clause
select: {d, o, h}

Result:

iex> [{%Delivery{id: 1}, _order, %HistoryItem{id: 1}, {%Delivery{id: 1}, _order, %HistoryItem{id: 2}]

Ecto documentation for the rescue! So what I found is https://hexdocs.pm/ecto/Ecto.Query.html#preload/3. Ecto is brilliant, so it can preload associations in the result set.

The final solution is

 from d in Delivery,
join: o in Order, on: o.id == d.order_id,
join: h in HistoryItem, on: h.delivery_id == d.id,
preload: [history_items: h, order: o],
where: … # Here my where clause
select: d

So I got a list of deliveries with preloaded orders and history items.
Happy coding :)

Ihor Katkov

Written by

I help organizations of all sizes to develop products that people love.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade