Counting records in many to many relation

I know that this question was already asked in this forum but I’m can’t seem to make it work on my end.

I have this typical Question and Tag model with many to many relation.

Question schema

defmodule Asker.Questions.Question do
  use Ecto.Schema
  import Ecto.Changeset

  schema "questions" do
    field :body, :string
    field :title, :string
    field :tags, {:array, :string}, virtual: true

    many_to_many :question_tags, Asker.Questions.Tag,
      join_through: "question_tags",
      on_replace: :delete

    timestamps()
  end

Tag schema

defmodule Asker.Questions.Tag do
  use Ecto.Schema
  import Ecto.Changeset

  schema "tags" do
    field :name, :string

    field :question_count, :integer, virtual: true
    many_to_many :questions, Asker.Questions.Question, join_through: "question_tags"
    timestamps()
  end

Here’s what I did.

  defp tags_with_count do
    from(t in Tag,
      left_join: qt in assoc(t, :questions),
      group_by: [t.id, qt.id],
      select_merge: %{question_count: count(qt.id)}
    )
  end

  def get_tags() do
    Tag
    |> Repo.all()
    |> Repo.preload(questions: tags_with_count())
  end

I’m aware that the code above will return an error. I just want to know how to reference the joined table in this case question_tags. Do I need to create another schema that fields on it are just belongs_to?

but I got this error instead:

** (Postgrex.Error) ERROR 42803 (grouping_error) column "q2.tag_id" must appear in the GROUP BY clause or be used in an aggregate function

    query: SELECT t0."id", t0."name", t0."inserted_at", t0."updated_at", count(q1."id"), q2."tag_id"::bigint FROM "tags" AS t0 LEFT OUTER JOIN "question_tags" AS q3 ON q3."tag_id" = t0."id" LEFT OUTER JOIN "questions" AS q1 ON q3."question_id" = q1."id" INNER JOIN "question_tags" AS q2 ON t0."id" = q2."question_id" WHERE (q2."tag_id" = ANY($1)) GROUP BY t0."id", q1."id" ORDER BY q2."tag_id"::bigint
    (ecto_sql 3.7.2) lib/ecto/adapters/sql.ex:760: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.7.2) lib/ecto/adapters/sql.ex:693: Ecto.Adapters.SQL.execute/5
    (ecto 3.7.2) lib/ecto/repo/queryable.ex:219: Ecto.Repo.Queryable.execute/4
    (ecto 3.7.2) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    (elixir 1.12.2) lib/enum.ex:1582: Enum."-map/2-lists^map/1-0-"/2

The expected result that I’m trying to get is like this

ID Name Count
1 Tag A 23
2 Tag B 52

Here’s what I did in psql that achieve the expected result that I wanted but I don’t know its Ecto equivalent.

asker_dev=# select a.id,  a.name AS tag, count(b.tag_id) from tags a left join question_tags b on a.id = b.tag_id group by a.name, a.id;
 id | tag | count 
----+-----+-------
 11 | f   |     1
  3 | b   |     0
  2 | c   |     0
  7 | d   |     1
  1 | a   |     1
  8 | e   |     0
1 Like

You can reference the join table directly in your query without needing an ecto schema. Based on your psql query something like this should work:

defp tags_with_count do
    from(t in Tag,
      left_join: qt in "question_tags",
      on: t.id == qt.tag_id, 
      select: %{id: t.id, name: t.name, count: count(qt.tag_id)},
      group_by: [t.id, t.name]
    )
  end
2 Likes