Packages
ecto_sql
3.5.0-rc.0
3.14.0
3.13.5
3.13.4
3.13.3
3.13.2
3.13.1
3.13.0
3.12.1
3.12.0
3.11.3
3.11.2
3.11.1
3.11.0
3.10.2
3.10.1
3.10.0
3.9.2
3.9.1
3.9.0
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.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.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.2
3.2.1
3.2.0
3.1.6
3.1.5
3.1.4
3.1.3
3.1.2
3.1.1
3.1.0
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
SQL-based adapters for Ecto and database migrations
Current section
Files
Jump to
Current section
Files
integration_test/sql/subquery.exs
defmodule Ecto.Integration.SubQueryTest 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
test "from: subqueries with select source" do
TestRepo.insert!(%Post{title: "hello", public: true})
query = from p in Post, select: p
assert ["hello"] =
TestRepo.all(from p in subquery(query), select: p.title)
assert [post] =
TestRepo.all(from p in subquery(query), select: p)
assert %NaiveDateTime{} = post.inserted_at
assert post.__meta__.state == :loaded
end
@tag :map_boolean_in_expression
test "from: subqueries with map and select expression" do
TestRepo.insert!(%Post{title: "hello", public: true})
query = from p in Post, select: %{title: p.title, pub: not p.public}
assert ["hello"] =
TestRepo.all(from p in subquery(query), select: p.title)
assert [%{title: "hello", pub: false}] =
TestRepo.all(from p in subquery(query), select: p)
assert [{"hello", %{title: "hello", pub: false}}] =
TestRepo.all(from p in subquery(query), select: {p.title, p})
assert [{%{title: "hello", pub: false}, false}] =
TestRepo.all(from p in subquery(query), select: {p, p.pub})
end
@tag :map_boolean_in_expression
test "from: subqueries with map update and select expression" do
TestRepo.insert!(%Post{title: "hello", public: true})
query = from p in Post, select: %{p | public: not p.public}
assert ["hello"] =
TestRepo.all(from p in subquery(query), select: p.title)
assert [%Post{title: "hello", public: false}] =
TestRepo.all(from p in subquery(query), select: p)
assert [{"hello", %Post{title: "hello", public: false}}] =
TestRepo.all(from p in subquery(query), select: {p.title, p})
assert [{%Post{title: "hello", public: false}, false}] =
TestRepo.all(from p in subquery(query), select: {p, p.public})
end
test "from: subqueries with map update on virtual field and select expression" do
TestRepo.insert!(%Post{title: "hello"})
query = from p in Post, select: %{p | temp: p.title}
assert ["hello"] =
TestRepo.all(from p in subquery(query), select: p.temp)
assert [%Post{title: "hello", temp: "hello"}] =
TestRepo.all(from p in subquery(query), select: p)
end
@tag :subquery_aggregates
test "from: subqueries with aggregates" do
TestRepo.insert!(%Post{visits: 10})
TestRepo.insert!(%Post{visits: 11})
TestRepo.insert!(%Post{visits: 13})
query = from p in Post, select: [:visits], order_by: [asc: :visits]
assert [13] = TestRepo.all(from p in subquery(query), select: max(p.visits))
query = from p in Post, select: [:visits], order_by: [asc: :visits], limit: 2
assert [11] = TestRepo.all(from p in subquery(query), select: max(p.visits))
query = from p in Post, order_by: [asc: :visits]
assert [13] = TestRepo.all(from p in subquery(query), select: max(p.visits))
query = from p in Post, order_by: [asc: :visits], limit: 2
assert [11] = TestRepo.all(from p in subquery(query), select: max(p.visits))
end
test "from: subqueries with parameters" do
TestRepo.insert!(%Post{visits: 10, title: "hello"})
TestRepo.insert!(%Post{visits: 11, title: "hello"})
TestRepo.insert!(%Post{visits: 13, title: "world"})
query = from p in Post, where: p.visits >= ^11 and p.visits <= ^13
query = from p in subquery(query), where: p.title == ^"hello", select: fragment("? + ?", p.visits, ^1)
assert [12] = TestRepo.all(query)
end
test "join: subqueries with select source" do
%{id: id} = TestRepo.insert!(%Post{title: "hello", public: true})
TestRepo.insert!(%Comment{post_id: id})
query = from p in Post, select: p
assert ["hello"] =
TestRepo.all(from c in Comment, join: p in subquery(query), on: c.post_id == p.id, select: p.title)
assert [%Post{inserted_at: %NaiveDateTime{}}] =
TestRepo.all(from c in Comment, join: p in subquery(query), on: c.post_id == p.id, select: p)
end
test "join: subqueries with parameters" do
TestRepo.insert!(%Post{visits: 10, title: "hello"})
TestRepo.insert!(%Post{visits: 11, title: "hello"})
TestRepo.insert!(%Post{visits: 13, title: "world"})
TestRepo.insert!(%Comment{})
TestRepo.insert!(%Comment{})
query = from p in Post, where: p.visits >= ^11 and p.visits <= ^13
query = from c in Comment,
join: p in subquery(query),
where: p.title == ^"hello",
select: fragment("? + ?", p.visits, ^1)
assert [12, 12] = TestRepo.all(query)
end
end