如何将 Where 子句子查询转换为 Ecto 连接?

Question

我目前有两个表具有 hasMany 关系：

customers [hasMany] items

items内部有一些belongsTo关系：

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

我的目标是有一个 Ecto 查询，该查询返回至少有一个items记录(status_id为 2 的 AND 以及此customers记录具有的任何其他items的customers列表。

我还需要预加载items、providers、statuses和types

我已经在SQL中复制了它，如下所示：

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);

这是我当前的 Ecto 查询中的一些逆向工程：

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
],
)
)

此 Ecto 查询为我提供了具有status_id为 2 的项目的客户，但它只返回符合条件的项目记录。如果其中一条记录的status_id为 2，我希望所有项目都与该客户相关联。

我试图通过使用子查询作为连接来实现这一点，如下所示：

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
]
)
)

但是，这不会返回任何结果。如果能提供一些 Ecto 专家的任何见解，我将不胜感激。

Answer 1

编辑：看起来你应该用on: p.id == i.customer_id,替换on: p.id == i.id,在你的join: ...

像这样的东西应该通过这个文档 Ecto 子查询工作

看起来我们可以通过联接过滤status_id为 2 的所有客户 + 项目，然后我们得到所有这些客户的列表，并预加载了他们的所有项目。

另请注意用于获取嵌套关联的更简单的预加载结构。

我成功地在具有类似结构的数据库上测试了它，希望它对您有用

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