Ecto query call when using aggregate function

Phoenix Forum Questions / Help
, ,
1

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.

2

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
1 Like
3

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.

4

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?

5

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
6

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