This is the organization that makes sense to me:
create table(:categories) do
add :title, :string
end
create table(:versions) do
add :title, :string
add :category_id, :id
end
create table(:records) do
add :title, :string
add :category_id, :id
add :version_id, :id
end
defmodule Squery do
import Ecto.Query;
alias Squery.{Repo}
def init do
Repo.insert_all(
"categories",[
[title: "Category 1"],
[title: "Category 2"],
[title: "Category 3"]
]
)
Repo.insert_all(
"versions",[
[title: "1.5", category_id: 1],
[title: "3.0", category_id: 1],
[title: "6.3", category_id: 1],
[title: "1.0", category_id: 2],
[title: "1.0", category_id: 3]
]
)
Repo.insert_all(
"records",[
[title: "A1.0", category_id: 1, version_id: 1],
[title: "A2.0", category_id: 1, version_id: 1],
[title: "A3.0", category_id: 1, version_id: 1],
[title: "A1.1", category_id: 1, version_id: 2],
[title: "A2.1", category_id: 1, version_id: 2],
[title: "A3.1", category_id: 1, version_id: 2],
[title: "A1.2", category_id: 1, version_id: 3],
[title: "A2.2", category_id: 1, version_id: 3],
[title: "A3.2", category_id: 1, version_id: 3],
[title: "A4.2", category_id: 1, version_id: 3],
[title: "B1.0", category_id: 2, version_id: 4],
[title: "B2.0", category_id: 2, version_id: 4],
[title: "C1.0", category_id: 3, version_id: 5]
]
)
:ok
end
def query1 do
"records"
|> group_by([:category_id, :version_id])
|> select([r], %{
category_id: r.category_id,
version_id: r.version_id,
record_count: count(r.id)
})
|> subquery()
|> join(:inner, [i], c in "categories", i.category_id == c.id)
|> join(:inner, [i], v in "versions", i.version_id == v.id)
|> select([i,c,v], %{
title: c.title,
version: v.title,
count: i.record_count
})
|> order_by([i,c,v],[asc: c.title, desc: v.id])
|> Repo.all()
end
def query2(category_id) do
with [%{id: max_version_id}|_] = versions <- versions(category_id),
counts <- counts(category_id, max_version_id),
records <- records(max_version_id) do
%{counts: counts, versions: versions, records: records}
else
_ ->
nil
end
end
defp versions(category_id) do
"versions"
|> where(category_id: ^category_id)
|> select([:title, :id])
|> order_by([desc: :id])
|> Repo.all()
end
defp counts(category_id, version_id) do
"records"
|> where([category_id: ^category_id])
|> group_by([:version_id])
|> select([r],%{
version_id: r.version_id,
record_count: count(r.id)
})
|> subquery()
|> select([i],%{
total: type(sum(i.record_count), :integer),
versions: count(i.version_id),
latest: type(
fragment("SUM(CASE WHEN ? = ? THEN ? ELSE 0 END)", i.version_id, ^version_id, i.record_count),
:integer
)
})
|> Repo.one()
end
defp records(version_id) do
"records"
|> where([version_id: ^version_id])
|> select([:id, :title, :version_id])
|> order_by(:id)
|> Repo.all()
end
end
iex(1)> Squery.init()
18:09:45.477 [debug] QUERY OK db=11.9ms
INSERT INTO "categories" ("title") VALUES ($1),($2),($3) ["Category 1", "Category 2", "Category 3"]
18:09:45.480 [debug] QUERY OK db=2.1ms
INSERT INTO "versions" ("category_id","title") VALUES ($1,$2),($3,$4),($5,$6),($7,$8),($9,$10) [1, "1.5", 1, "3.0", 1, "6.3", 2, "1.0", 3, "1.0"]
18:09:45.483 [debug] QUERY OK db=2.2ms
INSERT INTO "records" ("category_id","title","version_id") VALUES ($1,$2,$3),($4,$5,$6),($7,$8,$9),($10,$11,$12),($13,$14,$15),($16,$17,$18),($19,$20,$21),($22,$23,$24),($25,$26,$27),($28,$29,$30),($31,$32,$33),($34,$35,$36),($37,$38,$39) [1, "A1.0", 1, 1, "A2.0", 1, 1, "A3.0", 1, 1, "A1.1", 2, 1, "A2.1", 2, 1, "A3.1", 2, 1, "A1.2", 3, 1, "A2.2", 3, 1, "A3.2", 3, 1, "A4.2", 3, 2, "B1.0", 4, 2, "B2.0", 4, 3, "C1.0", 5]
:ok
iex(2)> Squery.query1()
18:09:55.634 [debug] QUERY OK db=3.4ms
SELECT c1."title", v2."title", s0."record_count" FROM (SELECT r0."category_id" AS "category_id", r0."version_id" AS "version_id", count(r0."id") AS "record_count" FROM "records" AS r0 GROUP BY r0."category_id", r0."version_id") AS s0 INNER JOIN "categories" AS c1 ON s0."category_id" = c1."id" INNER JOIN "versions" AS v2 ON s0."version_id" = v2."id" ORDER BY c1."title", v2."id" DESC []
[
%{count: 4, title: "Category 1", version: "6.3"},
%{count: 3, title: "Category 1", version: "3.0"},
%{count: 3, title: "Category 1", version: "1.5"},
%{count: 2, title: "Category 2", version: "1.0"},
%{count: 1, title: "Category 3", version: "1.0"}
]
iex(3)> Squery.query2(1)
18:10:10.934 [debug] QUERY OK source="versions" db=2.3ms decode=2.5ms
SELECT v0."title", v0."id" FROM "versions" AS v0 WHERE (v0."category_id" = $1) ORDER BY v0."id" DESC [1]
18:10:10.937 [debug] QUERY OK db=3.0ms
SELECT sum(s0."record_count")::bigint, count(s0."version_id"), SUM(CASE WHEN s0."version_id" = $1 THEN s0."record_count" ELSE 0 END)::bigint FROM (SELECT r0."version_id" AS "version_id", count(r0."id") AS "record_count" FROM "records" AS r0 WHERE (r0."category_id" = $2) GROUP BY r0."version_id") AS s0 [3, 1]
18:10:10.939 [debug] QUERY OK source="records" db=1.9ms
SELECT r0."id", r0."title", r0."version_id" FROM "records" AS r0 WHERE ((r0."category_id" = $1) AND (r0."version_id" = $2)) ORDER BY r0."id" [1, 3]
%{
counts: %{latest: 4, total: 10, versions: 3},
records: [
%{id: 7, title: "A1.2", version_id: 3},
%{id: 8, title: "A2.2", version_id: 3},
%{id: 9, title: "A3.2", version_id: 3},
%{id: 10, title: "A4.2", version_id: 3}
],
versions: [
%{id: 3, title: "6.3"},
%{id: 2, title: "3.0"},
%{id: 1, title: "1.5"}
]
}
iex(4)> Squery.query2(2)
18:10:14.148 [debug] QUERY OK source="versions" db=2.6ms
SELECT v0."title", v0."id" FROM "versions" AS v0 WHERE (v0."category_id" = $1) ORDER BY v0."id" DESC [2]
18:10:14.151 [debug] QUERY OK db=2.6ms
SELECT sum(s0."record_count")::bigint, count(s0."version_id"), SUM(CASE WHEN s0."version_id" = $1 THEN s0."record_count" ELSE 0 END)::bigint FROM (SELECT r0."version_id" AS "version_id", count(r0."id") AS "record_count" FROM "records" AS r0 WHERE (r0."category_id" = $2) GROUP BY r0."version_id") AS s0 [4, 2]
18:10:14.153 [debug] QUERY OK source="records" db=2.1ms
SELECT r0."id", r0."title", r0."version_id" FROM "records" AS r0 WHERE ((r0."category_id" = $1) AND (r0."version_id" = $2)) ORDER BY r0."id" [2, 4]
%{
counts: %{latest: 2, total: 2, versions: 1},
records: [
%{id: 11, title: "B1.0", version_id: 4},
%{id: 12, title: "B2.0", version_id: 4}
],
versions: [%{id: 4, title: "1.0"}]
}
iex(5)> Squery.query2(3)
18:10:18.088 [debug] QUERY OK source="versions" db=1.3ms
SELECT v0."title", v0."id" FROM "versions" AS v0 WHERE (v0."category_id" = $1) ORDER BY v0."id" DESC [3]
18:10:18.090 [debug] QUERY OK db=2.2ms
SELECT sum(s0."record_count")::bigint, count(s0."version_id"), SUM(CASE WHEN s0."version_id" = $1 THEN s0."record_count" ELSE 0 END)::bigint FROM (SELECT r0."version_id" AS "version_id", count(r0."id") AS "record_count" FROM "records" AS r0 WHERE (r0."category_id" = $2) GROUP BY r0."version_id") AS s0 [5, 3]
18:10:18.092 [debug] QUERY OK source="records" db=1.3ms
SELECT r0."id", r0."title", r0."version_id" FROM "records" AS r0 WHERE ((r0."category_id" = $1) AND (r0."version_id" = $2)) ORDER BY r0."id" [3, 5]
%{
counts: %{latest: 1, total: 1, versions: 1},
records: [%{id: 13, title: "C1.0", version_id: 5}],
versions: [%{id: 5, title: "1.0"}]
}
iex(6)>
