Packages
ecto
2.0.0-rc.3
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: true
alias Ecto.Integration.TestRepo
import Ecto.Query
alias Ecto.Integration.Post
alias Ecto.Integration.Comment
alias Ecto.Integration.Permalink
alias Ecto.Integration.User
@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: c.post_id in ^[post.id])
assert {2, nil} = TestRepo.delete_all(query)
assert [%Comment{}] = 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)
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]
# Without 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 == []
# Without 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})
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}],
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 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
end