I currently have two tables that have a hasMany relationship:
customers [hasMany] items
items there are some
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
statuses and a
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 some various iterations of the below:
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.