Questions & Help>Questions

How to do multi selects in Ecto

#ecto#troubleshooting
shahryarjb

shahryarjb

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 :

Untitled
Untitled1663×916 67.7 KB

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

  1. count all records of category one
  2. load and count all records of the category one which its version_id = 1 along with its fields
  3. 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.18

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
       },
   ]
]
6 comments
#ecto#troubleshooting
5127 6

Showing Posts 1 to 6

peerreynders

peerreynders

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.
1
Post #1
shahryarjb

shahryarjb OP

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
37%20pm638×599 87.4 KB

0
Post #2
shahryarjb

shahryarjb OP

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. :sweat::frowning_face:

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?

0
Post #3
peerreynders

peerreynders

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)>
4
Post #4
shahryarjb

shahryarjb OP

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.
0
Post #5
peerreynders

peerreynders

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
1
Post #6
— All posts loaded —

Where Next? Top

View thread on forum (has 6 replies!)
ecto
troubleshooting
Home Questions & Help>Questions
6 5058 6
Last post
REPLY VIEW THREAD WITH 6 POSTS

Trending in Questions Top

RSP87
Questions & Help>Questions

Programming Phoenix chat example - iPhone lack of socket updates
I’m working on a project that simulates the bumbl example in the programming phoenix book. It acts almost like an email client. We have a...
/phoenix#troubleshooting
1 950 6
New
kpanic
Questions & Help>Questions

"Mentoring" match maker? maybe MVP
Hi everyone, I am toying with the idea of building a “match maker” for giving personal help to people that wants to start coding. I sta...
#helpers#programming
8 940 4
New
nseaSeb
Questions & Help>Questions

What's the approach for handling lists that allows for optimized traversal?
Hello, I know there is an approach for handling lists that allows for optimized traversal, but I can’t recall the specific method (somet...
#learning-elixir#list
13 419 15
New
brecabral
Questions & Help>Questions

Scoped Routes section of the Phoenix guides refers to a problem without explaining what it is ("but there is a problem")
Documentation While reading the Scoped Routes section, I noticed that the documentation currently refers to a problem without explainin...
/phoenix
6 829 4
New
velrest
Questions & Help>Questions

`:erlang.float` vs `value * 1.0`: Which is the prefered way?
So my question is quite simple and i have found no conclusive answer on forum, google or AI. Should we use :erlang.float for Integer to ...
#learning-elixir
12 638 5
New
asweet-confluent
Questions & Help>Questions

Why is Logger's default level :debug?
I recently noticed that Elixir’s Logger defaults its primary log level to :debug when no :logger, :level application configuration is pre...
#logging#logger
13 504 10
New
apz
Questions & Help>Questions

Vscode elixirLS setup - want to fix/understand "couldn't create connection to server"
I’m new to elixir and just tried to install the elixirLS extension for VScode(ium) and it is throwing some errors that I would like help ...
#learning-elixir#vscode#elixir-ls
0 144 3
New

Other Trending Topics Top

JesseHerrick
News>Announcing

Dexter - A fast, full-featured Elixir LSP optimized for large codebases
Hey, I’m Jesse and I’m the main contributor behind Dexter, a full-featured, lightning-fast Elixir LSP optimized for large codebases. It s...
#library#lsp#dexter
187 6988 64
New
mudasobwa
Chat & Discussions>Discussions

Cure - a new language compiled to BEAM
I am happy to introduce the very α version of the new programming language compiled to BEAM. Welcome Cure. It has literally three kille...
#static-types#type-checking#types#fsm
171 6315 70
New
marciok
News>Announcing

Gust - A task orchestration system built in Elixir
Hi there! We created Gust: A task orchestrator inspired by Airflow. For those who have never heard about Aiflow, it’s a Python-based wor...
/phoenix/oban#library#workflow#background_job#reactor
111 4469 40
New
mhanberg
Chat & Discussions>Discussions

Expert LSP is now available!
Hi everyone! The first release candidate for the Expert language server project is now available! We’ve published a press release detai...
#lsp#expert
220 4348 38
New
jimsynz
News>Announcing

Beam Bots - Resilient Robotics on the BEAM
Beam Bots (or just BB for short) is a framework for building fault-tolerant robotics applications in Elixir using familiar OTP patterns. ...
/beam-bots#library#robotics#bb
119 2916 28
New
Dmk
News>Announcing

Xamal - Deploy Elixir releases to bare metal servers via SSH
Xamal is a deployment tool for Elixir apps that deploys native releases to bare metal servers over SSH. It’s a port of GitHub - basecamp/...
#library#deployment#deploy#deployments#xamal
75 2512 33
New
Back to top

Questions & Help>Questions

Questions Questions ❯

Latest on Elixir Forum

Elixir Forum ❯

Sponsor Spotlight

Dashbit

Build Elixir applications with speed and confidence.

Our Sponsors

Dashbit

Build Elixir applications with speed and confidence.

EEF

Supporting innovation across the BEAM ecosystem.

AppSignal

Catch errors, track performance, monitor hosts and more.

Honeybadger

Error tracking, logs, uptime, and Just Enough APM. Built for developers.

Code Sync

Elixir, Erlang & BEAM conferences from Code Sync - join us in 2026!

Defacto

Bringing learning, work and compliance together since 1987.

WyeWorks

**Building the future of software with AI, trust, and human connection.**

Potions

The deployment platform built for Elixir: PaaS ease, VPS control.

Software Mansion

The team behind Membrane, Popcorn, LiveDebugger. Available for hire.

Pragmatic Studio

Courses that'll move you from confusion to "Aha, now I get it!"

View all our sponsors ❯

Categories:

Sub Categories:

Forums

Popular Tags

Our Sponsors

Dashbit

Build Elixir applications with speed and confidence.

EEF

Supporting innovation across the BEAM ecosystem.

AppSignal

Catch errors, track performance, monitor hosts and more.

Honeybadger

Error tracking, logs, uptime, and Just Enough APM. Built for developers.

Code Sync

Elixir, Erlang & BEAM conferences from Code Sync - join us in 2026!

Defacto

Bringing learning, work and compliance together since 1987.

WyeWorks

**Building the future of software with AI, trust, and human connection.**

Potions

The deployment platform built for Elixir: PaaS ease, VPS control.

Software Mansion

The team behind Membrane, Popcorn, LiveDebugger. Available for hire.

Pragmatic Studio

Courses that'll move you from confusion to "Aha, now I get it!"

View all our sponsors ❯

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews