subquery.exs (4769B)
1 defmodule Ecto.Integration.SubQueryTest 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 alias Ecto.Integration.Post 7 alias Ecto.Integration.Comment 8 9 test "from: subqueries with select source" do 10 TestRepo.insert!(%Post{title: "hello", public: true}) 11 12 query = from p in Post, select: p 13 assert ["hello"] = 14 TestRepo.all(from p in subquery(query), select: p.title) 15 assert [post] = 16 TestRepo.all(from p in subquery(query), select: p) 17 18 assert %NaiveDateTime{} = post.inserted_at 19 assert post.__meta__.state == :loaded 20 end 21 22 @tag :map_boolean_in_expression 23 test "from: subqueries with map and select expression" do 24 TestRepo.insert!(%Post{title: "hello", public: true}) 25 26 query = from p in Post, select: %{title: p.title, pub: not p.public} 27 assert ["hello"] = 28 TestRepo.all(from p in subquery(query), select: p.title) 29 assert [%{title: "hello", pub: false}] = 30 TestRepo.all(from p in subquery(query), select: p) 31 assert [{"hello", %{title: "hello", pub: false}}] = 32 TestRepo.all(from p in subquery(query), select: {p.title, p}) 33 assert [{%{title: "hello", pub: false}, false}] = 34 TestRepo.all(from p in subquery(query), select: {p, p.pub}) 35 end 36 37 @tag :map_boolean_in_expression 38 test "from: subqueries with map update and select expression" do 39 TestRepo.insert!(%Post{title: "hello", public: true}) 40 41 query = from p in Post, select: %{p | public: not p.public} 42 assert ["hello"] = 43 TestRepo.all(from p in subquery(query), select: p.title) 44 assert [%Post{title: "hello", public: false}] = 45 TestRepo.all(from p in subquery(query), select: p) 46 assert [{"hello", %Post{title: "hello", public: false}}] = 47 TestRepo.all(from p in subquery(query), select: {p.title, p}) 48 assert [{%Post{title: "hello", public: false}, false}] = 49 TestRepo.all(from p in subquery(query), select: {p, p.public}) 50 end 51 52 test "from: subqueries with map update on virtual field and select expression" do 53 TestRepo.insert!(%Post{title: "hello"}) 54 55 query = from p in Post, select: %{p | temp: p.title} 56 assert ["hello"] = 57 TestRepo.all(from p in subquery(query), select: p.temp) 58 assert [%Post{title: "hello", temp: "hello"}] = 59 TestRepo.all(from p in subquery(query), select: p) 60 end 61 62 @tag :subquery_aggregates 63 test "from: subqueries with aggregates" do 64 TestRepo.insert!(%Post{visits: 10}) 65 TestRepo.insert!(%Post{visits: 11}) 66 TestRepo.insert!(%Post{visits: 13}) 67 68 query = from p in Post, select: [:visits], order_by: [asc: :visits] 69 assert [13] = TestRepo.all(from p in subquery(query), select: max(p.visits)) 70 query = from p in Post, select: [:visits], order_by: [asc: :visits], limit: 2 71 assert [11] = TestRepo.all(from p in subquery(query), select: max(p.visits)) 72 73 query = from p in Post, order_by: [asc: :visits] 74 assert [13] = TestRepo.all(from p in subquery(query), select: max(p.visits)) 75 query = from p in Post, order_by: [asc: :visits], limit: 2 76 assert [11] = TestRepo.all(from p in subquery(query), select: max(p.visits)) 77 end 78 79 test "from: subqueries with parameters" do 80 TestRepo.insert!(%Post{visits: 10, title: "hello"}) 81 TestRepo.insert!(%Post{visits: 11, title: "hello"}) 82 TestRepo.insert!(%Post{visits: 13, title: "world"}) 83 84 query = from p in Post, where: p.visits >= ^11 and p.visits <= ^13 85 query = from p in subquery(query), where: p.title == ^"hello", select: fragment("? + ?", p.visits, ^1) 86 assert [12] = TestRepo.all(query) 87 end 88 89 test "join: subqueries with select source" do 90 %{id: id} = TestRepo.insert!(%Post{title: "hello", public: true}) 91 TestRepo.insert!(%Comment{post_id: id}) 92 93 query = from p in Post, select: p 94 assert ["hello"] = 95 TestRepo.all(from c in Comment, join: p in subquery(query), on: c.post_id == p.id, select: p.title) 96 assert [%Post{inserted_at: %NaiveDateTime{}}] = 97 TestRepo.all(from c in Comment, join: p in subquery(query), on: c.post_id == p.id, select: p) 98 end 99 100 test "join: subqueries with parameters" do 101 TestRepo.insert!(%Post{visits: 10, title: "hello"}) 102 TestRepo.insert!(%Post{visits: 11, title: "hello"}) 103 TestRepo.insert!(%Post{visits: 13, title: "world"}) 104 TestRepo.insert!(%Comment{}) 105 TestRepo.insert!(%Comment{}) 106 107 query = from p in Post, where: p.visits >= ^11 and p.visits <= ^13 108 query = from c in Comment, 109 join: p in subquery(query), 110 where: p.title == ^"hello", 111 select: fragment("? + ?", p.visits, ^1) 112 assert [12, 12] = TestRepo.all(query) 113 end 114 end