Yama
Ecto query call when using aggregate function
Sorry for asking something that has been mentioned a few times here but would like some help to get a better understanding. What I’m trying to do is be able to return a list of tags with the number of posts that has a tag associated with it.
subquery = from(pt in PostTag, select: %{postCount: count(pt.tag_id)}, group_by: pt.tag_id)
query =
from(t in Tag,
join: pt in subquery(subquery),
on: t.id == pt.tag_id,
where: ilike(t.name, ^search),
select: %{id: t.id, name: t.name, count: pt.postCount},
group_by: t.id
)
We want to be able to return something like %{id: "1", count: 4} where the count is the number of posts associated with that tag. I currently have 3 tables, Post, Tag, and PostTags which connects the other two as many to many association.
I was hoping to get a better understanding of why I’m getting this error t0.id" must appear in the GROUP BY clause or be used in an aggregate function.
Thanks for the help and clarity in regards to this problem and pointing in the right direction in solving it.
Most Liked
Yama
I believe I may have found a solution. Would love some input if there is a better approach or maybe some flaws so what seems to be working. Thanks again
def get_tags(search) do
subquery =
from(pt in PostTag,
select: %{tag_id: pt.tag_id, postCount: count(pt.tag_id)},
group_by: pt.tag_id
)
query =
from(t in Tag,
join: pt in subquery(subquery),
on: t.id == pt.tag_id,
where: ilike(t.name, ^search),
select: %{id: t.id, name: t.name, count: pt.postCount}
)
# from(t in Tag, join: p in assoc(:posts), select: t.name)
Repo.all(query)
end
Last Post!
Nicodemus
I found this thread while researching options for adding aggregated results to a list, and would like to propose a different way to do it that doesn’t require any extra mapping or struct building for anyone else looking. Apologies for necro-posting.
First, add a virtual field to the Tag schema:
field :posts_count, :integer, virtual: true
Then simply merge the aggregate result into the existing list of returned structs, which if there is no select in the query it defaults to the full schema (equivalent to select: t.)
def list_tags_with_posts_count do
Repo.all(
from t in Tag,
left_join: p in assoc(t, :posts),
group_by: t.id,
order_by: [asc: :name],
select_merge: %{posts_count: count(p.id)}
)
end
Popular in Questions
Other popular topics
Latest Phoenix Threads
Latest on Elixir Forum
Sponsor Spotlight
Build Elixir applications with speed and confidence.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #elixirconf-us
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #security
- #hex
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Error tracking for Elixir devs who love to ship. Start your free account.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
Enabling companies to succeed by building software people love.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"