Ecto query call when using aggregate function

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

Nothing to do with ecto, that error was coming from your SQL server.

And I’d guess it was because the subquery made no export of tag_id, the select only returned postCount without any correlation of matching.

Personally I would not have had a subquery, rather just a (left_)join and group based on the id/name and aggregate (count) the post’s table.

I’m also interested and couldn’t find an example for such common use case in Ecto docs.
Is this efficient way to do it?

Do you mind sharing some code example, eg. a best practice of doing this with Ecto?

Also, do you recommend “caching” the count of related Posts on Tag schema, eg. Tag.posts_count and how to do it efficiently (maybe with Ecto.Multi upon PostTag insert?) in many-to-many relations?

Basically, I’d like to list all tags (could be hundreds) and have the count of relevant posts attached to each tag returned from the DB, and have the counts update (in a liveview) as posts being created with some tags. What’s the wiser way to implement this?

So, as recommended here… we can add a virtual posts_count field to Tag schema.
Also we need to convert the maps returned by the query into structs as follows:

def list_tags_with_posts_count do
  query =
    from(t in Tag,
      left_join: p in assoc(t, :posts),
      group_by: t.id,
      select: %{id: t.id, name: t.name, posts_count: count(p.id)},
      order_by: [asc: :name]
    )

  query
  |> Repo.all()
  |> Enum.map(fn tag -> struct(Tag, tag) end)
end

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