Grouping_error in absinthe / dadaloader

Hi guys. give a schema like this:

schema "communities" do
    field(:title, :string)
    field(:desc, :string)
    ....

    many_to_many(
      :posts,
      Post,
      join_through: "communities_posts",
      join_keys: [community_id: :id, post_id: :id]
    )
    timestamps(type: :utc_datetime)
end

Absinthe Schema :

object :community do
    field(:id, :id)
    field(:title, :string)
    field(:desc, :string)
    ... 
    field :posts_count, :integer do
      arg(:count, :count_type, default_value: :count)
      arg(:type, :community_type, default_value: :community)

      resolve(dataloader(CMS, :posts))
      middleware(M.ConvertToInt)
    end
end

dataloader query:

def query(Post, args) do
    Post
    |> select([p], count(p.id))
end

what i want is very simple: get the total postCount of the community, but a got GROUP BY errors :

[debug] ABSINTHE schema=MastaniServerWeb.Schema variables=%{}
---
{
  community(id: 3) {
    title
    postsCount
  }
}
---
[debug] QUERY OK source="communities" db=5.4ms
SELECT c0."id", c0."title", c0."desc", c0."user_id", c0."inserted_at", c0."updated_at" FROM "communities" AS c0 WHERE (c0."id" = $1) [3]
[debug] QUERY ERROR source="cms_posts" db=20.1ms
SELECT c1."id", count(c0."id") FROM "cms_posts" AS c0 INNER JOIN "communities" AS c1 ON c1."id" = ANY($1) INNER JOIN "communities_posts" AS c2 ON c2."community_id" = c1."id" WHERE (c2."post_id" = c0."id") ORDER BY c1."id" [[3]]
[info] Sent 200 in 909ms
[error] Task #PID<0.487.0> started from #PID<0.485.0> terminating
** (Postgrex.Error) ERROR 42803 (grouping_error): column "c1.id" must appear in the GROUP BY clause or be used in an aggregate function
    (ecto) lib/ecto/adapters/sql.ex:431: Ecto.Adapters.SQL.execute_and_cache/7
    (ecto) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4
    (elixir) lib/enum.ex:1294: Enum."-map/2-lists^map/1-0-"/2
    (dataloader) lib/dataloader/ecto.ex:398: Dataloader.Source.Dataloader.Ecto.run_batch/2
    (elixir) lib/task/supervised.ex:88: Task.Supervised.do_apply/2
    (elixir) lib/task/supervised.ex:38: Task.Supervised.reply/5
    (stdlib) proc_lib.erl:247: :proc_lib.init_p_do_apply/3
Function: &:erlang.apply/2
    Args: [#Function<5.80139747/1 in Dataloader.Source.Dataloader.Ecto.run/1>, [{{:assoc, MastaniServer.CMS.Community, #PID<0.475.0>, :posts, MastaniServer.CMS.Post, %{count: :count, type: :community}}, #MapSet<[{[3], %MastaniServer.CMS.Community{__meta__: #Ecto.Schema.Metadata<:loaded, "communities">, author: #Ecto.Association.NotLoaded<association :author is not loaded>, desc: "js community", editors: #Ecto.Association.NotLoaded<association :editors is not loaded>, id: 3, inserted_at: #DateTime<2018-02-06 11:25:46.662701Z>, posts: #Ecto.Association.NotLoaded<association :posts is not loaded>, subscribers: #Ecto.Association.NotLoaded<association :subscribers is not loaded>, title: "js", updated_at: #DateTime<2018-02-06 11:25:46.662785Z>, user_id: 1}}]>}]]

I replied on the issue but i’ll xpost here:


Hey @mydearxym this is going to require a custom batch function in order to do. Even if the query thing you hvae didn’t cause explosions it would still do the wrong thing, because it would just return the same total count of posts for all communities instead of the number of posts per community.

You can see this in the SQL query that’s run:

SELECT c1."id", count(c0."id") FROM "cms_posts" AS c0
INNER JOIN "communities" AS c1 ON c1."id" = ANY($1)
INNER JOIN "communities_posts" AS c2 ON c2."community_id" = c1."id"
WHERE (c2."post_id" = c0."id")
ORDER BY c1."id" [[3]]

It’s just doing a flat count(posts.id) when what we really need is a group by community id and a count of the posts.

Let’s go build a custom batch function!

Here’s what we need in the resolver:

resolve fn community, args, %{context: %{loader: loader}} ->
  loader
  |> Dataloader.load(CMS, {:one, Post}, posts_count: community.id)
  |> on_load(fn loader ->
    {:ok, Dataloader.get(loader, CMS, {:one, Post}, posts_count: community.id)
  end)
end

Then the custom batch function. When you setup your dataloader source you need to pass in run_batch: &run_batch/5 and then let’s build the run_batch:

def run_batch(Post, post_query, :posts_count, community_ids, repo_opts) do
  # do the query here
end

def run_batch(queryable, query, col, inputs, repo_opts) do
  Dataloader.Ecto.run_batch(Repo, queryable, query, col, inputs, repo_opts)
end

Here we’ve got 2 clauses, the main one to do this specific batch, and then the fall through to use the default. It’s gonna be the job of the first clause to handle the specific batching case we have. What this gives us is the main queryable we’re loading, the ecto query with any filtering and what not for that table, all the various community ids we actually care about, and then options to pass to Repo calls.

The goal here is to return a list of results each corresponding to the provided community_ids. So let’s fill that out:

def run_batch(Post, post_query, :posts_count, community_ids, repo_opts) do
  query = from p in post_query,
    join: c in assoc(p, :communities),
    where: c.id in ^community_ids,
    group_by: c.id,
    select: {c.id, [count(p.id)]}
  
  results =
    query
    |> Repo.all(repo_opts)
    |> Map.new

  for id <- community_ids, do: Map.get(results, id, [])
end

The query itself here is very close to what was happening before but the big difference is that we’re now grouping by the community id, so that when we select the count of posts we’re getting the count per community.

Any questions?

I left out handling of args cause I’m not entirely sure what they mean.

6 Likes

thanks for the magic!

1 Like