Load nested association and select count at the same time

You can look at count distinct.

I can’t find documentation about count distinct with ecto.

I would know how to do that with plain SQL, but I’m quite new with ecto and I’m not sure what is the proper manner to do.

I did something similar sometime ago.
I used a virtual field for the count then make a querry like:

....,
select_merge: %{factions_count: count(factions.id)
....

So your query could look like:

query =
  from instance in Instance,
    left_join: factions in assoc(instance, :factions),
    left_join: profils in assoc(factions, :profils),
    preload: [factions: {factions, profils: profils}],
    order_by: [desc: instance.id],
    select_merge: %{registered_profils: count(profils.id)

But to be honest I don’t know if this is the best way but it works. ^^

Ok, thanks a lot. It doesn’t work, but I think I undestand better

Now, the query looks like that:

query =
  from instance in Instance,
    left_join: factions in assoc(instance, :factions),
    left_join: registrations in assoc(factions, :registrations),
    preload: [factions: factions],
    select_merge: %{registrations_number: count(registrations.faction_id)},
    group_by: registrations.faction_id,
    order_by: [desc: instance.id]

And the real SQL query that ecto make is (after formatting):

SELECT
  i0."id",
  i0."game_data",
  i0."game_status",
  i0."metadata",
  i0."name",
  i0."opening_date",
  i0."status",
  i0."inserted_at",
  i0."updated_at",
  count(r2."faction_id"),
  f1."id",
  f1."capacity",
  f1."faction_ref",
  f1."instance_id",
  f1."inserted_at",
  f1."updated_at"
FROM "instances" AS i0
LEFT OUTER JOIN "factions" AS f1 ON f1."instance_id" = i0."id"
LEFT OUTER JOIN "registrations" AS r2 ON r2."faction_id" = f1."id"
WHERE ((i0."status" > 1))
GROUP BY r2."faction_id"
ORDER BY i0."id" DESC
LIMIT $1 OFFSET $2 [25, 0]

Note: I have plugins for custom filter and pagination, so the limit and the where clause come from that.

And I have this error : (Postgrex.Error) ERROR 42803 (grouping_error) column "i0.id" must appear in the GROUP BY clause or be used in an aggregate function.

I’m not sure if it’s the query that is not correct or if I need some subqueries to achieve that.

Note2: I changed a bit the schema since my first post. Instead of having a many_to_many relation between Factions and Profils, I created a real schema “registration” that belong_to Factions and Profils (I need to store other informations into registration).

As the error said now the issue comes from the group_by clause.

You can use it only on primary id or in aggregate function. Try to refine the query then.
Good luck. ^^

If that can help you here is my own working query.

# preload active ads count per category
  def list_categories(%Section{} = section) do
    query = from c in Category,
      where: c.section_id == ^section.id,
      left_join: a in assoc(c, :ads),
      where: is_nil(a.id) or a.status == ^:active,
      group_by: c.id,
      order_by: [c.weight, c.name],
      select_merge: %{ads_count: count(a.id)}

    Repo.all(query)
  end

section has many categories
category has many ads

Yep ok, but the thing is, you don’t load data from section, you only filter category by the section_id. I’m trying to list all sections (in your situation), with their corresponding categories AND the count of ads. Which is why Postegres is not very happy.

But thanks again for the help

I’m now looking on this topic : Ecto subqueries with virtual fields? - #9 by xadhoom which may help me eventually.

I’m now with that sort of query`(doesn’t work either):

query =
  from instance in Instance,
    left_join: factions in assoc(instance, :factions),
    preload: [factions: factions],
    select_merge: %{
      registrations_number:
        fragment(
          "SELECT count(r2.faction_id) FROM registrations AS r2 WHERE ? = r2.faction_id GROUP BY r2.faction_id",
          factions.id
        )
    },
    order_by: [desc: instance.id]

I’m trying to figure out if there is a way to assign the result of a select_merge to preload’s members (the virtual field registration_number is on the faction schema.

Ok in fact I also did that…
Here is my code:

  def list_sections do
    Section
    |> order_by([:weight])
    |> Repo.all()
  end

  # preload active ads count per category
  def list_sections(:ads_count) do
    preload_query = from c in Category,
      left_join: a in assoc(c, :ads),
      where: a.status == ^:active,
      or_where: is_nil(a.id),
      group_by: c.id,
      order_by: [c.weight, c.name],
      select_merge: %{ads_count: count(a.id)}

    list_sections() |> Repo.preload(categories: preload_query)
  end

But this means making 2 requests on the database.