How to do multi selects in Ecto

This part doesn’t make any sense.

• Given your data model version holds “zero or more” versions for any one category. So version.id = 1 would belong to exactly one category.
• It would therefore make more sense if you were interested in min(version.id) (or more likely max(version.id) for the specified single category.

unfortunately I didn’t understand what you said, I’m sorry.
version.title is a string field and version_id relationed with record table, In fact, this is a filter, if it doesn’t answer me, so what do I do? I’m confused.
I just need to get table records which relation with Version table in the Category concerned( category id = 2) . how can I do? I’m new to this field , I sorry and thank you for giving me your time.

37%20pm638×599 87.4 KB

Ok , hmm, now I know it , but what is your suggestion? but I think it isn’t only problem , my main problem is how to write query for this , even if this logic is wrong.I’m confused.

So, I am thinking my way may be right, because at first, I print all Version of category which relation, and my user selects each one and sends it.

My version_id is a UUID, for this purpose, I want to print all versions of category with other query. if my client didn’t send any version_id , I would select max(version_title) of category which relation it.

Is my idea correct?

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)> 

At first, I am very grateful to you.
it seems that I need to use Query 2, it works with normal ID like integer, but I am using UUID, or I have to use normal ID or I should find the way which I will be able to fix it.

Your Post and codes are the best practice for me, Thank you.

Edited

I edited like this :

	def versions(category_id) do
		query = from p in ChangelogVersion,
				where: p.cms_changelog_category_id == ^category_id,
				order_by: [desc: p.inserted_at],
				select: %{title: p.title, id: p.id}

		Repo.all(query)
	end

	defp counts(category_id, version_id) do
		query_sub = from p in ChangelogRecord,
				where: p.cms_changelog_category_id == ^category_id,
				group_by: [p.cms_changelog_version_id],
				select: %{
					version_id: p.cms_changelog_version_id,
 				 	record_count: count(p.id)
				}
		from i in subquery(query_sub), select: %{
				 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

Versions function is true, but counts function which has a problem shows me this :

** (Ecto.Query.CompileError) `Repo.one(%{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)})` is not a valid query expression

Edit 2

I edited again :

	defp counts(category_id, version_id) do
		query_sub = from p in ChangelogRecord,
				where: p.cms_changelog_category_id == ^category_id,
				group_by: [p.cms_changelog_version_id],
				select: %{
					version_id: p.cms_changelog_version_id,
 				 	record_count: count(p.id)
				}
		as = from p in ChangelogRecord,
			join: i in subquery(query_sub),
		 	select: %{
				 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(as)
	end

The previous problem was solved but …

    ** (ArgumentError) Postgrex expected a binary of 16 bytes, got "7271dde6-bb02-4b54-8c02-a94da2d8b9a5". Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.

It seems that PostgreSQL can’t compare UUIDs directly but only use them in JOINS. Try this version:

  defp counts(category_id, version_id) do
    "records"
    |> where([category_id: ^category_id])
    |> join(:left, [r], q in "records", r.id == q.id and r.version_id == ^version_id)
    |> group_by([r,q], [r.version_id, q.version_id])
    |> select([r,q],%{
         version_id: r.version_id,
         is_latest: not(is_nil(q.version_id)),
         record_count: count(r.id)
       })
    |> subquery()
    |> select([s],%{
         total: type(sum(s.record_count), :integer),
         versions: count(s.version_id),
         latest: type(
           fragment("SUM(CASE WHEN ? THEN ? ELSE 0 END)", s.is_latest, s.record_count),
           :integer
         )
       })
    |> Repo.one()
  end