Packages
ecto
2.2.2
3.14.1
3.14.0
3.13.6
3.13.5
3.13.4
3.13.3
3.13.2
3.13.1
3.13.0
3.12.6
3.12.5
3.12.4
3.12.3
3.12.2
3.12.1
3.12.0
3.11.2
3.11.1
3.11.0
3.10.3
3.10.2
3.10.1
3.10.0
3.9.6
3.9.5
3.9.4
3.9.3
3.9.2
3.9.1
3.9.0
3.8.4
3.8.3
3.8.2
3.8.1
3.8.0
3.7.2
3.7.1
3.7.0
3.6.2
3.6.1
3.6.0
3.5.8
3.5.7
3.5.6
3.5.5
3.5.4
3.5.3
3.5.2
3.5.1
3.5.0
3.5.0-rc.1
3.5.0-rc.0
3.4.6
3.4.5
3.4.4
3.4.3
3.4.2
3.4.1
3.4.0
3.3.4
3.3.3
3.3.2
3.3.1
3.3.0
3.2.5
3.2.4
3.2.3
3.2.2
3.2.1
3.2.0
3.1.7
3.1.6
3.1.5
3.1.4
3.1.3
3.1.2
3.1.1
3.1.0
3.0.9
3.0.8
3.0.7
3.0.6
3.0.5
3.0.4
3.0.3
3.0.2
3.0.1
3.0.0
3.0.0-rc.1
3.0.0-rc.0
2.2.12
2.2.11
2.2.10
2.2.9
2.2.8
2.2.7
2.2.6
2.2.5
2.2.4
2.2.3
2.2.2
2.2.1
2.2.0
2.2.0-rc.1
2.2.0-rc.0
2.1.6
2.1.5
2.1.4
2.1.3
2.1.2
2.1.1
2.1.0
2.1.0-rc.5
2.1.0-rc.4
2.1.0-rc.3
2.1.0-rc.2
2.1.0-rc.1
2.1.0-rc.0
2.0.6
2.0.5
2.0.4
2.0.3
2.0.2
2.0.1
2.0.0
2.0.0-rc.6
2.0.0-rc.5
2.0.0-rc.4
2.0.0-rc.3
2.0.0-rc.2
2.0.0-rc.1
2.0.0-rc.0
2.0.0-beta.2
2.0.0-beta.1
2.0.0-beta.0
1.1.9
1.1.8
1.1.7
1.1.6
1.1.5
1.1.4
1.1.3
1.1.2
1.1.1
1.1.0
1.0.7
1.0.6
1.0.5
1.0.4
1.0.3
1.0.2
1.0.1
1.0.0
0.16.0
0.15.0
0.14.3
0.14.2
0.14.1
0.14.0
0.13.1
0.13.0
0.12.1
0.12.0
0.12.0-rc
0.11.3
0.11.2
0.11.1
0.11.0
0.10.3
0.10.2
0.10.1
0.10.0
0.9.0
0.8.1
0.8.0
0.7.2
0.7.1
0.7.0
0.6.0
0.5.1
0.5.0
0.4.0
0.3.0
0.2.8
0.2.7
0.2.6
0.2.5
0.2.4
0.2.3
0.2.2
0.2.1
0.2.0
0.1.0
A toolkit for data mapping and language integrated query for Elixir
Current section
Files
Jump to
Current section
Files
integration_test/cases/joins.exs
defmodule Ecto.Integration.JoinsTest do
use Ecto.Integration.Case, async: Application.get_env(:ecto, :async_integration_tests, true)
alias Ecto.Integration.TestRepo
import Ecto.Query
alias Ecto.Integration.Post
alias Ecto.Integration.Comment
alias Ecto.Integration.Permalink
alias Ecto.Integration.User
alias Ecto.Integration.PostUserCompositePk
@tag :update_with_join
test "update all with joins" do
user = TestRepo.insert!(%User{name: "Tester"})
post = TestRepo.insert!(%Post{title: "foo"})
comment = TestRepo.insert!(%Comment{text: "hey", author_id: user.id, post_id: post.id})
another_post = TestRepo.insert!(%Post{title: "bar"})
another_comment = TestRepo.insert!(%Comment{text: "another", author_id: user.id, post_id: another_post.id})
query = from(c in Comment, join: u in User, on: u.id == c.author_id,
where: c.post_id in ^[post.id])
assert {1, nil} = TestRepo.update_all(query, set: [text: "hoo"])
assert %Comment{text: "hoo"} = TestRepo.get(Comment, comment.id)
assert %Comment{text: "another"} = TestRepo.get(Comment, another_comment.id)
end
@tag :delete_with_join
test "delete all with joins" do
user = TestRepo.insert!(%User{name: "Tester"})
post = TestRepo.insert!(%Post{title: "foo"})
TestRepo.insert!(%Comment{text: "hey", author_id: user.id, post_id: post.id})
TestRepo.insert!(%Comment{text: "foo", author_id: user.id, post_id: post.id})
TestRepo.insert!(%Comment{text: "bar", author_id: user.id})
query = from(c in Comment, join: u in User, on: u.id == c.author_id,
where: is_nil(c.post_id))
assert {1, nil} = TestRepo.delete_all(query)
assert [%Comment{}, %Comment{}] = TestRepo.all(Comment)
query = from(c in Comment, join: u in assoc(c, :author),
join: p in assoc(c, :post),
where: p.id in ^[post.id])
assert {2, nil} = TestRepo.delete_all(query)
assert [] = TestRepo.all(Comment)
end
test "joins" do
_p = TestRepo.insert!(%Post{title: "1"})
p2 = TestRepo.insert!(%Post{title: "2"})
c1 = TestRepo.insert!(%Permalink{url: "1", post_id: p2.id})
query = from(p in Post, join: c in assoc(p, :permalink), order_by: p.id, select: {p, c})
assert [{^p2, ^c1}] = TestRepo.all(query)
query = from(p in Post, join: c in assoc(p, :permalink), on: c.id == ^c1.id, select: {p, c})
assert [{^p2, ^c1}] = TestRepo.all(query)
end
test "joins with queries" do
p1 = TestRepo.insert!(%Post{title: "1"})
p2 = TestRepo.insert!(%Post{title: "2"})
c1 = TestRepo.insert!(%Permalink{url: "1", post_id: p2.id})
permalink = from c in Permalink, where: c.url == ^"1"
query = from(p in Post, join: c in ^permalink, on: c.post_id == p.id, select: {p, c})
assert [{^p2, ^c1}] = TestRepo.all(query)
query = from(p in Post, join: c in ^permalink, on: c.id == ^c1.id, order_by: p.title, select: {p, c})
assert [{^p1, ^c1}, {^p2, ^c1}] = TestRepo.all(query)
end
@tag :left_join
test "left joins with missing entries" do
p1 = TestRepo.insert!(%Post{title: "1"})
p2 = TestRepo.insert!(%Post{title: "2"})
c1 = TestRepo.insert!(%Permalink{url: "1", post_id: p2.id})
query = from(p in Post, left_join: c in assoc(p, :permalink), order_by: p.id, select: {p, c})
assert [{^p1, nil}, {^p2, ^c1}] = TestRepo.all(query)
end
@tag :right_join
test "right joins with missing entries" do
%Post{id: pid1} = TestRepo.insert!(%Post{title: "1"})
%Post{id: pid2} = TestRepo.insert!(%Post{title: "2"})
%Permalink{id: plid1} = TestRepo.insert!(%Permalink{url: "1", post_id: pid2})
TestRepo.insert!(%Comment{text: "1", post_id: pid1})
TestRepo.insert!(%Comment{text: "2", post_id: pid2})
TestRepo.insert!(%Comment{text: "3", post_id: nil})
query = from(p in Post, right_join: c in assoc(p, :comments),
preload: :permalink, order_by: c.id)
assert [p1, p2, nil] = TestRepo.all(query)
assert p1.id == pid1
assert p2.id == pid2
assert p1.permalink == nil
assert p2.permalink.id == plid1
end
## Associations joins
test "has_many association join" do
post = TestRepo.insert!(%Post{title: "1", text: "hi"})
c1 = TestRepo.insert!(%Comment{text: "hey", post_id: post.id})
c2 = TestRepo.insert!(%Comment{text: "heya", post_id: post.id})
query = from(p in Post, join: c in assoc(p, :comments), select: {p, c}, order_by: p.id)
[{^post, ^c1}, {^post, ^c2}] = TestRepo.all(query)
end
test "has_one association join" do
post = TestRepo.insert!(%Post{title: "1", text: "hi"})
p1 = TestRepo.insert!(%Permalink{url: "hey", post_id: post.id})
p2 = TestRepo.insert!(%Permalink{url: "heya", post_id: post.id})
query = from(p in Post, join: c in assoc(p, :permalink), select: {p, c}, order_by: c.id)
[{^post, ^p1}, {^post, ^p2}] = TestRepo.all(query)
end
test "belongs_to association join" do
post = TestRepo.insert!(%Post{title: "1"})
p1 = TestRepo.insert!(%Permalink{url: "hey", post_id: post.id})
p2 = TestRepo.insert!(%Permalink{url: "heya", post_id: post.id})
query = from(p in Permalink, join: c in assoc(p, :post), select: {p, c}, order_by: p.id)
[{^p1, ^post}, {^p2, ^post}] = TestRepo.all(query)
end
test "has_many through association join" do
p1 = TestRepo.insert!(%Post{})
p2 = TestRepo.insert!(%Post{})
u1 = TestRepo.insert!(%User{name: "zzz"})
u2 = TestRepo.insert!(%User{name: "aaa"})
%Comment{} = TestRepo.insert!(%Comment{post_id: p1.id, author_id: u1.id})
%Comment{} = TestRepo.insert!(%Comment{post_id: p1.id, author_id: u1.id})
%Comment{} = TestRepo.insert!(%Comment{post_id: p1.id, author_id: u2.id})
%Comment{} = TestRepo.insert!(%Comment{post_id: p2.id, author_id: u2.id})
query = from p in Post, join: a in assoc(p, :comments_authors), select: {p, a}, order_by: [p.id, a.name]
assert [{^p1, ^u2}, {^p1, ^u1}, {^p1, ^u1}, {^p2, ^u2}] = TestRepo.all(query)
end
test "many_to_many association join" do
p1 = TestRepo.insert!(%Post{title: "1", text: "hi"})
p2 = TestRepo.insert!(%Post{title: "2", text: "ola"})
_p = TestRepo.insert!(%Post{title: "3", text: "hello"})
u1 = TestRepo.insert!(%User{name: "john"})
u2 = TestRepo.insert!(%User{name: "mary"})
TestRepo.insert_all "posts_users", [[post_id: p1.id, user_id: u1.id],
[post_id: p1.id, user_id: u2.id],
[post_id: p2.id, user_id: u2.id]]
query = from(p in Post, join: u in assoc(p, :users), select: {p, u}, order_by: p.id)
[{^p1, ^u1}, {^p1, ^u2}, {^p2, ^u2}] = TestRepo.all(query)
end
## Association preload
test "has_many assoc selector" do
p1 = TestRepo.insert!(%Post{title: "1"})
p2 = TestRepo.insert!(%Post{title: "2"})
c1 = TestRepo.insert!(%Comment{text: "1", post_id: p1.id})
c2 = TestRepo.insert!(%Comment{text: "2", post_id: p1.id})
c3 = TestRepo.insert!(%Comment{text: "3", post_id: p2.id})
# Without on
query = from(p in Post, join: c in assoc(p, :comments), preload: [comments: c])
[p1, p2] = TestRepo.all(query)
assert p1.comments == [c1, c2]
assert p2.comments == [c3]
# With on
query = from(p in Post, left_join: c in assoc(p, :comments),
on: p.title == c.text, preload: [comments: c])
[p1, p2] = TestRepo.all(query)
assert p1.comments == [c1]
assert p2.comments == []
end
test "has_one assoc selector" do
p1 = TestRepo.insert!(%Post{title: "1"})
p2 = TestRepo.insert!(%Post{title: "2"})
pl1 = TestRepo.insert!(%Permalink{url: "1", post_id: p1.id})
_pl = TestRepo.insert!(%Permalink{url: "2"})
pl3 = TestRepo.insert!(%Permalink{url: "3", post_id: p2.id})
query = from(p in Post, join: pl in assoc(p, :permalink), preload: [permalink: pl])
assert [post1, post3] = TestRepo.all(query)
assert post1.permalink == pl1
assert post3.permalink == pl3
end
test "belongs_to assoc selector" do
p1 = TestRepo.insert!(%Post{title: "1"})
p2 = TestRepo.insert!(%Post{title: "2"})
TestRepo.insert!(%Permalink{url: "1", post_id: p1.id})
TestRepo.insert!(%Permalink{url: "2"})
TestRepo.insert!(%Permalink{url: "3", post_id: p2.id})
query = from(pl in Permalink, left_join: p in assoc(pl, :post), preload: [post: p], order_by: pl.id)
assert [pl1, pl2, pl3] = TestRepo.all(query)
assert pl1.post == p1
refute pl2.post
assert pl3.post == p2
end
test "many_to_many assoc selector" do
p1 = TestRepo.insert!(%Post{title: "1"})
p2 = TestRepo.insert!(%Post{title: "2"})
_p = TestRepo.insert!(%Post{title: "3"})
u1 = TestRepo.insert!(%User{name: "1"})
u2 = TestRepo.insert!(%User{name: "2"})
TestRepo.insert_all "posts_users", [[post_id: p1.id, user_id: u1.id],
[post_id: p1.id, user_id: u2.id],
[post_id: p2.id, user_id: u2.id]]
# Without on
query = from(p in Post, left_join: u in assoc(p, :users), preload: [users: u], order_by: p.id)
[p1, p2, p3] = TestRepo.all(query)
assert p1.users == [u1, u2]
assert p2.users == [u2]
assert p3.users == []
# With on
query = from(p in Post, left_join: u in assoc(p, :users), on: p.title == u.name,
preload: [users: u], order_by: p.id)
[p1, p2, p3] = TestRepo.all(query)
assert p1.users == [u1]
assert p2.users == [u2]
assert p3.users == []
end
test "has_many through assoc selector" do
p1 = TestRepo.insert!(%Post{title: "1"})
p2 = TestRepo.insert!(%Post{title: "2"})
u1 = TestRepo.insert!(%User{name: "1"})
u2 = TestRepo.insert!(%User{name: "2"})
TestRepo.insert!(%Comment{post_id: p1.id, author_id: u1.id})
TestRepo.insert!(%Comment{post_id: p1.id, author_id: u1.id})
TestRepo.insert!(%Comment{post_id: p1.id, author_id: u2.id})
TestRepo.insert!(%Comment{post_id: p2.id, author_id: u2.id})
# Without on
query = from(p in Post, left_join: ca in assoc(p, :comments_authors),
preload: [comments_authors: ca])
[p1, p2] = TestRepo.all(query)
assert p1.comments_authors == [u1, u2]
assert p2.comments_authors == [u2]
# With on
query = from(p in Post, left_join: ca in assoc(p, :comments_authors),
on: ca.name == p.title, preload: [comments_authors: ca])
[p1, p2] = TestRepo.all(query)
assert p1.comments_authors == [u1]
assert p2.comments_authors == [u2]
end
test "has_many through-through assoc selector" do
%Post{id: pid1} = TestRepo.insert!(%Post{})
%Post{id: pid2} = TestRepo.insert!(%Post{})
%Permalink{} = TestRepo.insert!(%Permalink{post_id: pid1})
%Permalink{} = TestRepo.insert!(%Permalink{post_id: pid2})
%User{id: uid1} = TestRepo.insert!(%User{})
%User{id: uid2} = TestRepo.insert!(%User{})
%Comment{} = TestRepo.insert!(%Comment{post_id: pid1, author_id: uid1})
%Comment{} = TestRepo.insert!(%Comment{post_id: pid1, author_id: uid1})
%Comment{} = TestRepo.insert!(%Comment{post_id: pid1, author_id: uid2})
%Comment{} = TestRepo.insert!(%Comment{post_id: pid2, author_id: uid2})
query = from(p in Permalink, left_join: ca in assoc(p, :post_comments_authors),
preload: [post_comments_authors: ca], order_by: ca.id)
[l1, l2] = TestRepo.all(query)
[u1, u2] = l1.post_comments_authors
assert u1.id == uid1
assert u2.id == uid2
[u2] = l2.post_comments_authors
assert u2.id == uid2
# Insert some intermediary joins to check indexes won't be shuffled
query = from(p in Permalink,
left_join: assoc(p, :post),
left_join: ca in assoc(p, :post_comments_authors),
left_join: assoc(p, :post),
left_join: assoc(p, :post),
preload: [post_comments_authors: ca], order_by: ca.id)
[l1, l2] = TestRepo.all(query)
[u1, u2] = l1.post_comments_authors
assert u1.id == uid1
assert u2.id == uid2
[u2] = l2.post_comments_authors
assert u2.id == uid2
end
## Nested
test "nested assoc" do
%Post{id: pid1} = TestRepo.insert!(%Post{title: "1"})
%Post{id: pid2} = TestRepo.insert!(%Post{title: "2"})
%User{id: uid1} = TestRepo.insert!(%User{name: "1"})
%User{id: uid2} = TestRepo.insert!(%User{name: "2"})
%Comment{id: cid1} = TestRepo.insert!(%Comment{text: "1", post_id: pid1, author_id: uid1})
%Comment{id: cid2} = TestRepo.insert!(%Comment{text: "2", post_id: pid1, author_id: uid2})
%Comment{id: cid3} = TestRepo.insert!(%Comment{text: "3", post_id: pid2, author_id: uid2})
# use multiple associations to force parallel preloader
query = from p in Post,
left_join: c in assoc(p, :comments),
left_join: u in assoc(c, :author),
order_by: [p.id, c.id, u.id],
preload: [:permalink, comments: {c, author: {u, [:comments, :custom]}}],
select: {0, [p], 1, 2}
posts = TestRepo.all(query)
assert [p1, p2] = Enum.map(posts, fn {0, [p], 1, 2} -> p end)
assert p1.id == pid1
assert p2.id == pid2
assert [c1, c2] = p1.comments
assert [c3] = p2.comments
assert c1.id == cid1
assert c2.id == cid2
assert c3.id == cid3
assert c1.author.id == uid1
assert c2.author.id == uid2
assert c3.author.id == uid2
end
test "nested assoc with missing entries" do
%Post{id: pid1} = TestRepo.insert!(%Post{title: "1"})
%Post{id: pid2} = TestRepo.insert!(%Post{title: "2"})
%Post{id: pid3} = TestRepo.insert!(%Post{title: "2"})
%User{id: uid1} = TestRepo.insert!(%User{name: "1"})
%User{id: uid2} = TestRepo.insert!(%User{name: "2"})
%Comment{id: cid1} = TestRepo.insert!(%Comment{text: "1", post_id: pid1, author_id: uid1})
%Comment{id: cid2} = TestRepo.insert!(%Comment{text: "2", post_id: pid1, author_id: nil})
%Comment{id: cid3} = TestRepo.insert!(%Comment{text: "3", post_id: pid3, author_id: uid2})
query = from p in Post,
left_join: c in assoc(p, :comments),
left_join: u in assoc(c, :author),
order_by: [p.id, c.id, u.id],
preload: [comments: {c, author: u}]
assert [p1, p2, p3] = TestRepo.all(query)
assert p1.id == pid1
assert p2.id == pid2
assert p3.id == pid3
assert [c1, c2] = p1.comments
assert [] = p2.comments
assert [c3] = p3.comments
assert c1.id == cid1
assert c2.id == cid2
assert c3.id == cid3
assert c1.author.id == uid1
assert c2.author == nil
assert c3.author.id == uid2
end
test "nested assoc with child preload" do
%Post{id: pid1} = TestRepo.insert!(%Post{title: "1"})
%Post{id: pid2} = TestRepo.insert!(%Post{title: "2"})
%User{id: uid1} = TestRepo.insert!(%User{name: "1"})
%User{id: uid2} = TestRepo.insert!(%User{name: "2"})
%Comment{id: cid1} = TestRepo.insert!(%Comment{text: "1", post_id: pid1, author_id: uid1})
%Comment{id: cid2} = TestRepo.insert!(%Comment{text: "2", post_id: pid1, author_id: uid2})
%Comment{id: cid3} = TestRepo.insert!(%Comment{text: "3", post_id: pid2, author_id: uid2})
query = from p in Post,
left_join: c in assoc(p, :comments),
order_by: [p.id, c.id],
preload: [comments: {c, :author}],
select: p
assert [p1, p2] = TestRepo.all(query)
assert p1.id == pid1
assert p2.id == pid2
assert [c1, c2] = p1.comments
assert [c3] = p2.comments
assert c1.id == cid1
assert c2.id == cid2
assert c3.id == cid3
assert c1.author.id == uid1
assert c2.author.id == uid2
assert c3.author.id == uid2
end
test "nested assoc with sibling preload" do
%Post{id: pid1} = TestRepo.insert!(%Post{title: "1"})
%Post{id: pid2} = TestRepo.insert!(%Post{title: "2"})
%Permalink{id: plid1} = TestRepo.insert!(%Permalink{url: "1", post_id: pid2})
%Comment{id: cid1} = TestRepo.insert!(%Comment{text: "1", post_id: pid1})
%Comment{id: cid2} = TestRepo.insert!(%Comment{text: "2", post_id: pid2})
%Comment{id: _} = TestRepo.insert!(%Comment{text: "3", post_id: pid2})
query = from p in Post,
left_join: c in assoc(p, :comments),
where: c.text in ~w(1 2),
preload: [:permalink, comments: c],
select: {0, [p], 1, 2}
posts = TestRepo.all(query)
assert [p1, p2] = Enum.map(posts, fn {0, [p], 1, 2} -> p end)
assert p1.id == pid1
assert p2.id == pid2
assert p2.permalink.id == plid1
assert [c1] = p1.comments
assert [c2] = p2.comments
assert c1.id == cid1
assert c2.id == cid2
end
test "association with composite pk join" do
post = TestRepo.insert!(%Post{title: "1", text: "hi"})
user = TestRepo.insert!(%User{name: "1"})
TestRepo.insert!(%PostUserCompositePk{post_id: post.id, user_id: user.id})
query = from(p in Post, join: a in assoc(p, :post_user_composite_pk),
preload: [post_user_composite_pk: a], select: p)
assert [post] = TestRepo.all(query)
assert post.post_user_composite_pk
end
end