Ecto.Query连接会生成太多的内部连接
Ecto.Query连接会生成太多的内部连接
提问于 2017-11-15 05:49:51
不确定Ecto为什么返回不正确的结果。我可能会把事情搞砸了
这是我的Ecto.Query
from u in User,
join: c in assoc(u, :companies),
join: r in assoc(c, :roles),
join: a in assoc(r, :assets),
where: u.id == ^2 and a.id == ^1,
group_by: [c.id, u.id, r.id],
select: {u.email, u.id, r.name, c.name}它产生：
SELECT u0."email", u0."id", r2."name", c1."name"
FROM "users" AS u0
INNER JOIN "access_lists" AS a4 ON a4."user_id" = u0."id"
INNER JOIN "companies" AS c1 ON a4."company_id" = c1."id"
INNER JOIN "access_lists" AS a5 ON a5."company_id" = c1."id"
INNER JOIN "roles" AS r2 ON a5."role_id" = r2."id"
INNER JOIN "access_lists" AS a6 ON a6."role_id" = r2."id"
INNER JOIN "assets" AS a3 ON a6."asset_id" = a3."id"
WHERE ((u0."id" = $1) AND (a3."id" = $2))
GROUP BY c1."id", u0."id", r2."id" [2, 1]此查询的结果不正确。
这是正确的查询：
SELECT u0.email, u0.id, r2.name, c1.name, a6.asset_name , a6.id as asset_id
FROM users AS u0
INNER JOIN access_lists AS a4 ON a4.user_id = u0.id
INNER JOIN assets AS a6 ON a4.asset_id = a6.id
INNER JOIN companies AS c1 ON a4.company_id = c1.id
INNER JOIN roles AS r2 ON a4.role_id = r2.id
WHERE ((u0.id = 2) AND (a6.id = 1))
Group By u0.id, r2.id, c1.id, a6.id下面是模式：
AccessList：
schema "access_lists" do
belongs_to :user, Db.User
belongs_to :role, Db.Role
belongs_to :asset, Db.Asset
belongs_to :project, Db.Project
belongs_to :company, Db.Company
timestamps()
end用户：
many_to_many :roles, Db.Role, join_through: Db.AccessList
many_to_many :assets, Db.Asset, join_through: Db.AccessList
many_to_many :projects, Db.Project, join_through: Db.AccessList
many_to_many :companies, Db.Company, join_through: Db.AccessList 资产：
many_to_many :users, Db.User, join_through: Db.AccessList
many_to_many :companies, Db.Company, join_through: Db.AccessList
many_to_many :roles, Db.Role, join_through: Db.AccessList
many_to_many :projects, Db.Project, join_through: Db.AccessList 公司：
many_to_many :users, Db.User, join_through: Db.AccessList
many_to_many :assets, Db.Asset, join_through: Db.AccessList
many_to_many :roles, Db.Role, join_through: Db.AccessList
many_to_many :projects, Db.Project, join_through: Db.AccessList 角色：
many_to_many :users, Db.User, join_through: Db.AccessList
many_to_many :assets, Db.Asset, join_through: Db.AccessList
many_to_many :projects, Db.Project, join_through: Db.AccessList
many_to_many :companies, Db.Company, join_through: Db.AccessList 下面是一个图表：
回答 2
Stack Overflow用户
回答已采纳
发布于 2017-11-15 09:13:05
我用过
from u in User,
join: acl in AccessList, on: acl.user_id == u.id,
join: c in Company, on: acl.company_id == c.id,
join: r in Role, on: acl.role_id == r.id,
join: a in Asset, on: acl.asset_id == a.id,
where: u.id == ^2 and a.id == ^1,
group_by: [c.id, u.id, r.id],
select: {u.email, u.id, r.name, c.name}这是可行的，我得到了正确的结果，但我不知道为什么。
为什么我要定义每一个连接
为什么我不能用join assoc代替
Stack Overflow用户
发布于 2017-11-15 14:53:08
如果没有看到模式，很难判断，但我可以做一些猜测。由于AccessList是User与公司、角色和资产之间的连接表，所以Ecto不足以在后续联接中重用以前的AccessList查询。
尝试像这样显式地将它映射出来：
from u in User,
join: acl in assoc(u, :access_lists),
join: c in assoc(acl, :companies),
join: r in assoc(acl, :roles),
join: a in assoc(acl, :assets),
where: u.id == ^2 and a.id == ^1,
group_by: [c.id, u.id, r.id],
select: {u.email, u.id, r.name, c.name}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/47300267
复制相关文章
相似问题
腾讯云开发者
