Struggling to convert a WHERE clause subquery to an Ecto Join

Questions / Help
1

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 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.

2

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.

3

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 == c.id,
  preload: [items: [:provider, :type, :status]]
) |> Repo.all

which did the trick nicely!

2 Likes