joins.exs (21953B)
1 defmodule Ecto.Integration.JoinsTest do 2 use Ecto.Integration.Case, async: Application.compile_env(:ecto, :async_integration_tests, true) 3 4 alias Ecto.Integration.TestRepo 5 import Ecto.Query 6 7 alias Ecto.Integration.Post 8 alias Ecto.Integration.Comment 9 alias Ecto.Integration.Permalink 10 alias Ecto.Integration.User 11 alias Ecto.Integration.PostUserCompositePk 12 13 @tag :update_with_join 14 test "update all with joins" do 15 user = TestRepo.insert!(%User{name: "Tester"}) 16 post = TestRepo.insert!(%Post{title: "foo"}) 17 comment = TestRepo.insert!(%Comment{text: "hey", author_id: user.id, post_id: post.id}) 18 19 another_post = TestRepo.insert!(%Post{title: "bar"}) 20 another_comment = TestRepo.insert!(%Comment{text: "another", author_id: user.id, post_id: another_post.id}) 21 22 query = from(c in Comment, join: u in User, on: u.id == c.author_id, 23 where: c.post_id in ^[post.id]) 24 25 assert {1, nil} = TestRepo.update_all(query, set: [text: "hoo"]) 26 assert %Comment{text: "hoo"} = TestRepo.get(Comment, comment.id) 27 assert %Comment{text: "another"} = TestRepo.get(Comment, another_comment.id) 28 end 29 30 @tag :delete_with_join 31 test "delete all with joins" do 32 user = TestRepo.insert!(%User{name: "Tester"}) 33 post = TestRepo.insert!(%Post{title: "foo"}) 34 TestRepo.insert!(%Comment{text: "hey", author_id: user.id, post_id: post.id}) 35 TestRepo.insert!(%Comment{text: "foo", author_id: user.id, post_id: post.id}) 36 TestRepo.insert!(%Comment{text: "bar", author_id: user.id}) 37 38 query = from(c in Comment, join: u in User, on: u.id == c.author_id, 39 where: is_nil(c.post_id)) 40 assert {1, nil} = TestRepo.delete_all(query) 41 assert [%Comment{}, %Comment{}] = TestRepo.all(Comment) 42 43 query = from(c in Comment, join: u in assoc(c, :author), 44 join: p in assoc(c, :post), 45 where: p.id in ^[post.id]) 46 assert {2, nil} = TestRepo.delete_all(query) 47 assert [] = TestRepo.all(Comment) 48 end 49 50 test "joins" do 51 _p = TestRepo.insert!(%Post{title: "1"}) 52 p2 = TestRepo.insert!(%Post{title: "2"}) 53 c1 = TestRepo.insert!(%Permalink{url: "1", post_id: p2.id}) 54 55 query = from(p in Post, join: c in assoc(p, :permalink), order_by: p.id, select: {p, c}) 56 assert [{^p2, ^c1}] = TestRepo.all(query) 57 58 query = from(p in Post, join: c in assoc(p, :permalink), on: c.id == ^c1.id, select: {p, c}) 59 assert [{^p2, ^c1}] = TestRepo.all(query) 60 end 61 62 test "joins with queries" do 63 p1 = TestRepo.insert!(%Post{title: "1"}) 64 p2 = TestRepo.insert!(%Post{title: "2"}) 65 c1 = TestRepo.insert!(%Permalink{url: "1", post_id: p2.id}) 66 67 # Joined query without parameter 68 permalink = from c in Permalink, where: c.url == "1" 69 70 query = from(p in Post, join: c in ^permalink, on: c.post_id == p.id, select: {p, c}) 71 assert [{^p2, ^c1}] = TestRepo.all(query) 72 73 # Joined query with parameter 74 permalink = from c in Permalink, where: c.url == "1" 75 76 query = from(p in Post, join: c in ^permalink, on: c.id == ^c1.id, order_by: p.title, select: {p, c}) 77 assert [{^p1, ^c1}, {^p2, ^c1}] = TestRepo.all(query) 78 end 79 80 test "named joins" do 81 _p = TestRepo.insert!(%Post{title: "1"}) 82 p2 = TestRepo.insert!(%Post{title: "2"}) 83 c1 = TestRepo.insert!(%Permalink{url: "1", post_id: p2.id}) 84 85 query = 86 from(p in Post, join: c in assoc(p, :permalink), as: :permalink, order_by: p.id) 87 |> select([p, permalink: c], {p, c}) 88 89 assert [{^p2, ^c1}] = TestRepo.all(query) 90 end 91 92 test "joins with dynamic in :on" do 93 p = TestRepo.insert!(%Post{title: "1"}) 94 c = TestRepo.insert!(%Permalink{url: "1", post_id: p.id}) 95 96 join_on = dynamic([p, ..., c], c.id == ^c.id) 97 98 query = 99 from(p in Post, join: c in Permalink, on: ^join_on) 100 |> select([p, c], {p, c}) 101 102 assert [{^p, ^c}] = TestRepo.all(query) 103 104 join_on = dynamic([p, permalink: c], c.id == ^c.id) 105 106 query = 107 from(p in Post, join: c in Permalink, as: :permalink, on: ^join_on) 108 |> select([p, c], {p, c}) 109 110 assert [{^p, ^c}] = TestRepo.all(query) 111 end 112 113 @tag :cross_join 114 test "cross joins with missing entries" do 115 p1 = TestRepo.insert!(%Post{title: "1"}) 116 p2 = TestRepo.insert!(%Post{title: "2"}) 117 c1 = TestRepo.insert!(%Permalink{url: "1", post_id: p2.id}) 118 119 query = from(p in Post, cross_join: c in Permalink, order_by: p.id, select: {p, c}) 120 assert [{^p1, ^c1}, {^p2, ^c1}] = TestRepo.all(query) 121 end 122 123 @tag :left_join 124 test "left joins with missing entries" do 125 p1 = TestRepo.insert!(%Post{title: "1"}) 126 p2 = TestRepo.insert!(%Post{title: "2"}) 127 c1 = TestRepo.insert!(%Permalink{url: "1", post_id: p2.id}) 128 129 query = from(p in Post, left_join: c in assoc(p, :permalink), order_by: p.id, select: {p, c}) 130 assert [{^p1, nil}, {^p2, ^c1}] = TestRepo.all(query) 131 end 132 133 @tag :left_join 134 test "left join with missing entries from subquery" do 135 p1 = TestRepo.insert!(%Post{title: "1"}) 136 p2 = TestRepo.insert!(%Post{title: "2"}) 137 c1 = TestRepo.insert!(%Permalink{url: "1", post_id: p2.id}) 138 139 query = from(p in Post, left_join: c in subquery(Permalink), on: p.id == c.post_id, order_by: p.id, select: {p, c}) 140 assert [{^p1, nil}, {^p2, ^c1}] = TestRepo.all(query) 141 end 142 143 @tag :right_join 144 test "right joins with missing entries" do 145 %Post{id: pid1} = TestRepo.insert!(%Post{title: "1"}) 146 %Post{id: pid2} = TestRepo.insert!(%Post{title: "2"}) 147 148 %Permalink{id: plid1} = TestRepo.insert!(%Permalink{url: "1", post_id: pid2}) 149 150 TestRepo.insert!(%Comment{text: "1", post_id: pid1}) 151 TestRepo.insert!(%Comment{text: "2", post_id: pid2}) 152 TestRepo.insert!(%Comment{text: "3", post_id: nil}) 153 154 query = from(p in Post, right_join: c in assoc(p, :comments), 155 preload: :permalink, order_by: c.id) 156 assert [p1, p2, p3] = TestRepo.all(query) 157 assert p1.id == pid1 158 assert p2.id == pid2 159 assert is_nil(p3.id) 160 161 assert p1.permalink == nil 162 assert p2.permalink.id == plid1 163 end 164 165 ## Associations joins 166 167 test "has_many association join" do 168 post = TestRepo.insert!(%Post{title: "1"}) 169 c1 = TestRepo.insert!(%Comment{text: "hey", post_id: post.id}) 170 c2 = TestRepo.insert!(%Comment{text: "heya", post_id: post.id}) 171 172 query = from(p in Post, join: c in assoc(p, :comments), select: {p, c}, order_by: p.id) 173 [{^post, ^c1}, {^post, ^c2}] = TestRepo.all(query) 174 end 175 176 test "has_one association join" do 177 post1 = TestRepo.insert!(%Post{title: "1"}) 178 post2 = TestRepo.insert!(%Post{title: "1"}) 179 user = TestRepo.insert!(%User{}) 180 p1 = TestRepo.insert!(%Permalink{url: "hey", user_id: user.id, post_id: post1.id}) 181 p2 = TestRepo.insert!(%Permalink{url: "heya", user_id: user.id, post_id: post2.id}) 182 183 query = from(p in User, join: c in assoc(p, :permalink), select: {p, c}, order_by: c.id) 184 [{^user, ^p1}, {^user, ^p2}] = TestRepo.all(query) 185 end 186 187 test "belongs_to association join" do 188 post1 = TestRepo.insert!(%Post{title: "1"}) 189 post2 = TestRepo.insert!(%Post{title: "1"}) 190 user = TestRepo.insert!(%User{}) 191 p1 = TestRepo.insert!(%Permalink{url: "hey", user_id: user.id, post_id: post1.id}) 192 p2 = TestRepo.insert!(%Permalink{url: "heya", user_id: user.id, post_id: post2.id}) 193 194 query = from(p in Permalink, join: c in assoc(p, :user), select: {p, c}, order_by: p.id) 195 [{^p1, ^user}, {^p2, ^user}] = TestRepo.all(query) 196 end 197 198 test "has_many through association join" do 199 p1 = TestRepo.insert!(%Post{}) 200 p2 = TestRepo.insert!(%Post{}) 201 202 u1 = TestRepo.insert!(%User{name: "zzz"}) 203 u2 = TestRepo.insert!(%User{name: "aaa"}) 204 205 %Comment{} = TestRepo.insert!(%Comment{post_id: p1.id, author_id: u1.id}) 206 %Comment{} = TestRepo.insert!(%Comment{post_id: p1.id, author_id: u1.id}) 207 %Comment{} = TestRepo.insert!(%Comment{post_id: p1.id, author_id: u2.id}) 208 %Comment{} = TestRepo.insert!(%Comment{post_id: p2.id, author_id: u2.id}) 209 210 query = from p in Post, join: a in assoc(p, :comments_authors), select: {p, a}, order_by: [p.id, a.name] 211 assert [{^p1, ^u2}, {^p1, ^u1}, {^p1, ^u1}, {^p2, ^u2}] = TestRepo.all(query) 212 end 213 214 test "has_many through nested association joins" do 215 u1 = TestRepo.insert!(%User{name: "Alice"}) 216 u2 = TestRepo.insert!(%User{name: "John"}) 217 218 p1 = TestRepo.insert!(%Post{title: "p1", author_id: u1.id}) 219 p2 = TestRepo.insert!(%Post{title: "p2", author_id: u1.id}) 220 221 TestRepo.insert!(%Comment{text: "c1", author_id: u1.id, post_id: p1.id}) 222 TestRepo.insert!(%Comment{text: "c2", author_id: u2.id, post_id: p1.id}) 223 TestRepo.insert!(%Comment{text: "c3", author_id: u2.id, post_id: p2.id}) 224 TestRepo.insert!(%Comment{text: "c4", post_id: p2.id}) 225 TestRepo.insert!(%Comment{text: "c5", author_id: u1.id, post_id: p2.id}) 226 227 assert %{ 228 comments: [ 229 %{text: "c1"}, 230 %{text: "c5"} 231 ], 232 posts: [ 233 %{title: "p1"} = p1, 234 %{title: "p2"} = p2 235 ] 236 } = 237 from(u in User) 238 |> join(:left, [u], p in assoc(u, :posts)) 239 |> join(:left, [u], c in assoc(u, :comments)) 240 |> join(:left, [_, p], c in assoc(p, :comments)) 241 |> preload( 242 [user, posts, comments, post_comments], 243 comments: comments, 244 posts: {posts, comments: {post_comments, :author}} 245 ) 246 |> TestRepo.get(u1.id) 247 248 assert [ 249 %{text: "c1", author: %{name: "Alice"}}, 250 %{text: "c2", author: %{name: "John"}} 251 ] = Enum.sort_by(p1.comments, & &1.text) 252 253 assert [ 254 %{text: "c3", author: %{name: "John"}}, 255 %{text: "c4", author: nil}, 256 %{text: "c5", author: %{name: "Alice"}} 257 ] = Enum.sort_by(p2.comments, & &1.text) 258 end 259 260 test "many_to_many association join" do 261 p1 = TestRepo.insert!(%Post{title: "1"}) 262 p2 = TestRepo.insert!(%Post{title: "2"}) 263 _p = TestRepo.insert!(%Post{title: "3"}) 264 u1 = TestRepo.insert!(%User{name: "john"}) 265 u2 = TestRepo.insert!(%User{name: "mary"}) 266 267 TestRepo.insert_all "posts_users", [[post_id: p1.id, user_id: u1.id], 268 [post_id: p1.id, user_id: u2.id], 269 [post_id: p2.id, user_id: u2.id]] 270 271 query = from(p in Post, join: u in assoc(p, :users), select: {p, u}, order_by: p.id) 272 [{^p1, ^u1}, {^p1, ^u2}, {^p2, ^u2}] = TestRepo.all(query) 273 end 274 275 ## Association preload 276 277 test "has_many assoc selector" do 278 p1 = TestRepo.insert!(%Post{title: "1"}) 279 p2 = TestRepo.insert!(%Post{title: "2"}) 280 281 c1 = TestRepo.insert!(%Comment{text: "1", post_id: p1.id}) 282 c2 = TestRepo.insert!(%Comment{text: "2", post_id: p1.id}) 283 c3 = TestRepo.insert!(%Comment{text: "3", post_id: p2.id}) 284 285 # Without on 286 query = from(p in Post, join: c in assoc(p, :comments), preload: [comments: c]) 287 [p1, p2] = TestRepo.all(query) 288 assert p1.comments == [c1, c2] 289 assert p2.comments == [c3] 290 291 # With on 292 query = from(p in Post, left_join: c in assoc(p, :comments), 293 on: p.title == c.text, preload: [comments: c]) 294 [p1, p2] = TestRepo.all(query) 295 assert p1.comments == [c1] 296 assert p2.comments == [] 297 end 298 299 test "has_one assoc selector" do 300 p1 = TestRepo.insert!(%Post{title: "1"}) 301 p2 = TestRepo.insert!(%Post{title: "2"}) 302 303 pl1 = TestRepo.insert!(%Permalink{url: "1", post_id: p1.id}) 304 _pl = TestRepo.insert!(%Permalink{url: "2"}) 305 pl3 = TestRepo.insert!(%Permalink{url: "3", post_id: p2.id}) 306 307 query = from(p in Post, join: pl in assoc(p, :permalink), preload: [permalink: pl]) 308 assert [post1, post3] = TestRepo.all(query) 309 310 assert post1.permalink == pl1 311 assert post3.permalink == pl3 312 end 313 314 test "belongs_to assoc selector" do 315 p1 = TestRepo.insert!(%Post{title: "1"}) 316 p2 = TestRepo.insert!(%Post{title: "2"}) 317 318 TestRepo.insert!(%Permalink{url: "1", post_id: p1.id}) 319 TestRepo.insert!(%Permalink{url: "2"}) 320 TestRepo.insert!(%Permalink{url: "3", post_id: p2.id}) 321 322 query = from(pl in Permalink, left_join: p in assoc(pl, :post), preload: [post: p], order_by: pl.id) 323 assert [pl1, pl2, pl3] = TestRepo.all(query) 324 325 assert pl1.post == p1 326 refute pl2.post 327 assert pl3.post == p2 328 end 329 330 test "many_to_many assoc selector" do 331 p1 = TestRepo.insert!(%Post{title: "1"}) 332 p2 = TestRepo.insert!(%Post{title: "2"}) 333 _p = TestRepo.insert!(%Post{title: "3"}) 334 u1 = TestRepo.insert!(%User{name: "1"}) 335 u2 = TestRepo.insert!(%User{name: "2"}) 336 337 TestRepo.insert_all "posts_users", [[post_id: p1.id, user_id: u1.id], 338 [post_id: p1.id, user_id: u2.id], 339 [post_id: p2.id, user_id: u2.id]] 340 341 # Without on 342 query = from(p in Post, left_join: u in assoc(p, :users), preload: [users: u], order_by: p.id) 343 [p1, p2, p3] = TestRepo.all(query) 344 assert Enum.sort_by(p1.users, & &1.name) == [u1, u2] 345 assert p2.users == [u2] 346 assert p3.users == [] 347 348 # With on 349 query = from(p in Post, left_join: u in assoc(p, :users), on: p.title == u.name, 350 preload: [users: u], order_by: p.id) 351 [p1, p2, p3] = TestRepo.all(query) 352 assert p1.users == [u1] 353 assert p2.users == [u2] 354 assert p3.users == [] 355 end 356 357 test "has_many through assoc selector" do 358 p1 = TestRepo.insert!(%Post{title: "1"}) 359 p2 = TestRepo.insert!(%Post{title: "2"}) 360 361 u1 = TestRepo.insert!(%User{name: "1"}) 362 u2 = TestRepo.insert!(%User{name: "2"}) 363 364 TestRepo.insert!(%Comment{post_id: p1.id, author_id: u1.id}) 365 TestRepo.insert!(%Comment{post_id: p1.id, author_id: u1.id}) 366 TestRepo.insert!(%Comment{post_id: p1.id, author_id: u2.id}) 367 TestRepo.insert!(%Comment{post_id: p2.id, author_id: u2.id}) 368 369 # Without on 370 query = from(p in Post, left_join: ca in assoc(p, :comments_authors), 371 preload: [comments_authors: ca]) 372 [p1, p2] = TestRepo.all(query) 373 assert p1.comments_authors == [u1, u2] 374 assert p2.comments_authors == [u2] 375 376 # With on 377 query = from(p in Post, left_join: ca in assoc(p, :comments_authors), 378 on: ca.name == p.title, preload: [comments_authors: ca]) 379 [p1, p2] = TestRepo.all(query) 380 assert p1.comments_authors == [u1] 381 assert p2.comments_authors == [u2] 382 end 383 384 test "has_many through-through assoc selector" do 385 %Post{id: pid1} = TestRepo.insert!(%Post{}) 386 %Post{id: pid2} = TestRepo.insert!(%Post{}) 387 388 %Permalink{} = TestRepo.insert!(%Permalink{post_id: pid1, url: "1"}) 389 %Permalink{} = TestRepo.insert!(%Permalink{post_id: pid2, url: "2"}) 390 391 %User{id: uid1} = TestRepo.insert!(%User{}) 392 %User{id: uid2} = TestRepo.insert!(%User{}) 393 394 %Comment{} = TestRepo.insert!(%Comment{post_id: pid1, author_id: uid1}) 395 %Comment{} = TestRepo.insert!(%Comment{post_id: pid1, author_id: uid1}) 396 %Comment{} = TestRepo.insert!(%Comment{post_id: pid1, author_id: uid2}) 397 %Comment{} = TestRepo.insert!(%Comment{post_id: pid2, author_id: uid2}) 398 399 query = from(p in Permalink, left_join: ca in assoc(p, :post_comments_authors), 400 preload: [post_comments_authors: ca], order_by: ca.id) 401 402 [l1, l2] = TestRepo.all(query) 403 [u1, u2] = l1.post_comments_authors 404 assert u1.id == uid1 405 assert u2.id == uid2 406 407 [u2] = l2.post_comments_authors 408 assert u2.id == uid2 409 410 # Insert some intermediary joins to check indexes won't be shuffled 411 query = from(p in Permalink, 412 left_join: assoc(p, :post), 413 left_join: ca in assoc(p, :post_comments_authors), 414 left_join: assoc(p, :post), 415 left_join: assoc(p, :post), 416 preload: [post_comments_authors: ca], order_by: ca.id) 417 418 [l1, l2] = TestRepo.all(query) 419 [u1, u2] = l1.post_comments_authors 420 assert u1.id == uid1 421 assert u2.id == uid2 422 423 [u2] = l2.post_comments_authors 424 assert u2.id == uid2 425 end 426 427 ## Nested 428 429 test "nested assoc" do 430 %Post{id: pid1} = TestRepo.insert!(%Post{title: "1"}) 431 %Post{id: pid2} = TestRepo.insert!(%Post{title: "2"}) 432 433 %User{id: uid1} = TestRepo.insert!(%User{name: "1"}) 434 %User{id: uid2} = TestRepo.insert!(%User{name: "2"}) 435 436 %Comment{id: cid1} = TestRepo.insert!(%Comment{text: "1", post_id: pid1, author_id: uid1}) 437 %Comment{id: cid2} = TestRepo.insert!(%Comment{text: "2", post_id: pid1, author_id: uid2}) 438 %Comment{id: cid3} = TestRepo.insert!(%Comment{text: "3", post_id: pid2, author_id: uid2}) 439 440 # use multiple associations to force parallel preloader 441 query = from p in Post, 442 left_join: c in assoc(p, :comments), 443 left_join: u in assoc(c, :author), 444 order_by: [p.id, c.id, u.id], 445 preload: [:permalink, comments: {c, author: {u, [:comments, :custom]}}], 446 select: {0, [p], 1, 2} 447 448 posts = TestRepo.all(query) 449 assert [p1, p2] = Enum.map(posts, fn {0, [p], 1, 2} -> p end) 450 assert p1.id == pid1 451 assert p2.id == pid2 452 453 assert [c1, c2] = p1.comments 454 assert [c3] = p2.comments 455 assert c1.id == cid1 456 assert c2.id == cid2 457 assert c3.id == cid3 458 459 assert c1.author.id == uid1 460 assert c2.author.id == uid2 461 assert c3.author.id == uid2 462 end 463 464 test "nested assoc with missing entries" do 465 %Post{id: pid1} = TestRepo.insert!(%Post{title: "1"}) 466 %Post{id: pid2} = TestRepo.insert!(%Post{title: "2"}) 467 %Post{id: pid3} = TestRepo.insert!(%Post{title: "2"}) 468 469 %User{id: uid1} = TestRepo.insert!(%User{name: "1"}) 470 %User{id: uid2} = TestRepo.insert!(%User{name: "2"}) 471 472 %Comment{id: cid1} = TestRepo.insert!(%Comment{text: "1", post_id: pid1, author_id: uid1}) 473 %Comment{id: cid2} = TestRepo.insert!(%Comment{text: "2", post_id: pid1, author_id: nil}) 474 %Comment{id: cid3} = TestRepo.insert!(%Comment{text: "3", post_id: pid3, author_id: uid2}) 475 476 query = from p in Post, 477 left_join: c in assoc(p, :comments), 478 left_join: u in assoc(c, :author), 479 order_by: [p.id, c.id, u.id], 480 preload: [comments: {c, author: u}] 481 482 assert [p1, p2, p3] = TestRepo.all(query) 483 assert p1.id == pid1 484 assert p2.id == pid2 485 assert p3.id == pid3 486 487 assert [c1, c2] = p1.comments 488 assert [] = p2.comments 489 assert [c3] = p3.comments 490 assert c1.id == cid1 491 assert c2.id == cid2 492 assert c3.id == cid3 493 494 assert c1.author.id == uid1 495 assert c2.author == nil 496 assert c3.author.id == uid2 497 end 498 499 test "nested assoc with child preload" do 500 %Post{id: pid1} = TestRepo.insert!(%Post{title: "1"}) 501 %Post{id: pid2} = TestRepo.insert!(%Post{title: "2"}) 502 503 %User{id: uid1} = TestRepo.insert!(%User{name: "1"}) 504 %User{id: uid2} = TestRepo.insert!(%User{name: "2"}) 505 506 %Comment{id: cid1} = TestRepo.insert!(%Comment{text: "1", post_id: pid1, author_id: uid1}) 507 %Comment{id: cid2} = TestRepo.insert!(%Comment{text: "2", post_id: pid1, author_id: uid2}) 508 %Comment{id: cid3} = TestRepo.insert!(%Comment{text: "3", post_id: pid2, author_id: uid2}) 509 510 query = from p in Post, 511 left_join: c in assoc(p, :comments), 512 order_by: [p.id, c.id], 513 preload: [comments: {c, :author}], 514 select: p 515 516 assert [p1, p2] = TestRepo.all(query) 517 assert p1.id == pid1 518 assert p2.id == pid2 519 520 assert [c1, c2] = p1.comments 521 assert [c3] = p2.comments 522 assert c1.id == cid1 523 assert c2.id == cid2 524 assert c3.id == cid3 525 526 assert c1.author.id == uid1 527 assert c2.author.id == uid2 528 assert c3.author.id == uid2 529 end 530 531 test "nested assoc with sibling preload" do 532 %Post{id: pid1} = TestRepo.insert!(%Post{title: "1"}) 533 %Post{id: pid2} = TestRepo.insert!(%Post{title: "2"}) 534 535 %Permalink{id: plid1} = TestRepo.insert!(%Permalink{url: "1", post_id: pid2}) 536 537 %Comment{id: cid1} = TestRepo.insert!(%Comment{text: "1", post_id: pid1}) 538 %Comment{id: cid2} = TestRepo.insert!(%Comment{text: "2", post_id: pid2}) 539 %Comment{id: _} = TestRepo.insert!(%Comment{text: "3", post_id: pid2}) 540 541 query = from p in Post, 542 left_join: c in assoc(p, :comments), 543 where: c.text in ~w(1 2), 544 preload: [:permalink, comments: c], 545 select: {0, [p], 1, 2} 546 547 posts = TestRepo.all(query) 548 assert [p1, p2] = Enum.map(posts, fn {0, [p], 1, 2} -> p end) 549 assert p1.id == pid1 550 assert p2.id == pid2 551 552 assert p2.permalink.id == plid1 553 554 assert [c1] = p1.comments 555 assert [c2] = p2.comments 556 assert c1.id == cid1 557 assert c2.id == cid2 558 end 559 560 test "mixing regular join and assoc selector" do 561 p1 = TestRepo.insert!(%Post{title: "1"}) 562 p2 = TestRepo.insert!(%Post{title: "2"}) 563 564 c1 = TestRepo.insert!(%Comment{text: "1", post_id: p1.id}) 565 c2 = TestRepo.insert!(%Comment{text: "2", post_id: p1.id}) 566 c3 = TestRepo.insert!(%Comment{text: "3", post_id: p2.id}) 567 568 pl1 = TestRepo.insert!(%Permalink{url: "1", post_id: p1.id}) 569 _pl = TestRepo.insert!(%Permalink{url: "2"}) 570 pl3 = TestRepo.insert!(%Permalink{url: "3", post_id: p2.id}) 571 572 # Without on 573 query = from(p in Post, join: pl in assoc(p, :permalink), 574 join: c in assoc(p, :comments), 575 preload: [permalink: pl], 576 select: {p, c}) 577 [{p1, ^c1}, {p1, ^c2}, {p2, ^c3}] = TestRepo.all(query) 578 assert p1.permalink == pl1 579 assert p2.permalink == pl3 580 end 581 582 test "association with composite pk join" do 583 post = TestRepo.insert!(%Post{title: "1"}) 584 user = TestRepo.insert!(%User{name: "1"}) 585 TestRepo.insert!(%PostUserCompositePk{post_id: post.id, user_id: user.id}) 586 587 query = from(p in Post, join: a in assoc(p, :post_user_composite_pk), 588 preload: [post_user_composite_pk: a], select: p) 589 assert [post] = TestRepo.all(query) 590 assert post.post_user_composite_pk 591 end 592 end