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