How do I translate a Where clause subquery into 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:

SELECT
distinct on (c0.id)
  c0."id", 
  c0."inserted_at", 
  c0."updated_at", 
  c1."id", 
  c1."inserted_at", 
  c1."updated_at"
FROM "customers" AS c0
  INNER JOIN "items" AS c1 ON c1."customer_id" = c0."id"
WHERE EXISTS (
  SELECT * 
  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:

Repo.all( 
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 follows:

Repo.all( 
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: p.id == i.id,
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.

Solution

Edit: Also looks like you should replace on: p.id == i.id, with on: p.id == i.customer_id, in your join: ...

Something like this should work going by this documentation Ecto subquery

Looks like we can filter all the customers+items where status_id is 2 with a join, then we get the list of all those customers with all their items preloaded.

Also note the simpler preload structure for getting nested associations.

I tested it on a database of mine with similar structure successfully, hope it works for you

import Ecto.Query

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