How to do multi selects in Ecto

shahryarjb · May 17, 2018, 2:44pm

hello,
I have a challenge which I should do it. I need multi values in my db with Ecto, but I need to pass it like one request
please see my image :

for example when my user calls my router link , I should pass this :

count all records of category one
load and count all records of the category one which its version_id = 1 along with its fields
lists of all version along with its title and id

Do I send 3 Repo.all to my db with Ecto or something else?

I want to create like this : EasyBlog - Changelog for 6.0.10

My client only sends Category Id and I should pass like this Json example :

Category_id == 1
[
   category_info: [
      count_all_records: 100,
      count_all_records_by_version_one: 10
      load_list_of_version: [
         {
            name: "1.5",
            id: 1
         },
         {
            name: "1.7",
            id: 2
         },
         {
            name: "1.6",
            id: 3
         },
         {
            name: "3",
            id: 4
         }
      ]
   ]
   category_records: [
      {
            name: "bug fix 1",
            id: 1
            version_id: 1
       },
       {
            name: "bug fix 2",
            id: 3
            version_id: 3
       },
       {
            name: "bug fix 3",
            id: 10
            version_id: 3
       },
   ]
]

peerreynders · May 17, 2018, 5:44pm

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.

shahryarjb · May 17, 2018, 6:48pm

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%20pm

shahryarjb · May 17, 2018, 6:58pm

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?

peerreynders · May 17, 2018, 10:15pm

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

shahryarjb · May 18, 2018, 7:51am

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.

peerreynders · May 18, 2018, 11:18am

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