Struggling to convert a WHERE clause subquery to an Ecto Join

I currently have two tables that have a hasMany relationship:

customers [hasMany] items

Within items there are some belongsTo relationships:

providers [belongsTo] items
statuses [belongsTo] items
types [belongsTo] items

My goal is to have an Ecto query that returns a list of customers that have at least one items record with a status_id of 2 AND any other items that this customers record has.

I also need to preload items, providers, statuses and a types

I have replicated this in SQL as follows:

distinct on (
FROM "customers" AS c0
  INNER JOIN "items" AS c1 ON c1."customer_id" = c0."id"
  FROM "items" c2
  WHERE c2."customer_id" = c0."id"
    AND c2.status_id = 2);

Which was a bit of reverse engineering from my current Ecto query:

from(p in Customers,
inner_join: r in assoc(p, :items),
where: r.status_id == 2,
preload: [
  items: r, items: :provider, items: :type, items: :status

This Ecto query gives me the customers that have an item with a status_id of 2 but it only returns the qualifying item record. I would like to have all of the items associated with that customer if one of the records has a status_id of 2.

I tried to achieve this by using the subquery as a join as some various iterations of the below:

from(p in Customers,
inner_join: r in assoc(p, :items),
join: i in subquery(from(i in MyApp.Items, where: i.status_id == 2 )), on: ==,
preload: [
  items: r, items: :provider, items: :type, items: :status

However this returns no results. I’d be very grateful for any insight from some Ecto experts.

I think the problem comes from using r as preload for items. Because it’s using a join to get the records and you are also using a where statement that filters the join to return only an item with status_id = 2.

I guess it could be solved by removing this part items: r, from preload in the first query. Which will run a second query to retrieve all items for returned customers.

Thanks for your reply! You were correct that he confluence of joins was causing an issue. A user at SO pointed me to:

import Ecto.Query

from(c in Customers,
  join: i in subquery(
    from(i in Items,
      where: i.status_id == 2
  on: i.customer_id ==,
  preload: [items: [:provider, :type, :status]]
) |> Repo.all

which did the trick nicely!