abdelaz3r

Load nested association and select count at the same time

Hello.

I have this Ecto Shema:

Instance -> has_many(Faction) -> many_to_many(Profils)

So, instances has many factions, and profils can register into a faction (given an instance).

I would like, when I load instances, to preload their corresponding factions (easy) and to add, for each faction, a field that is the number of registered profil.

First question: I dont know if I need to add a field (virtual?) to the faction schema (such as “registered_profils”).

Second question: How would look like the request?

For now I have this request that works (but it only preload factions into instances and profils into factions):

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]

I also tried different queries such as:

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

But none works for now. Any idea?

9 comments

/phoenix #ecto #ecto-query #nested-associations

0 3380 9

2019-08-30 15:32:20 UTC

First Post!

LostKobrakai

You can look at count distinct.

Post #1

Most Liked

Kurisu

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. ^^

Post #3

abdelaz3r

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).

Post #4

abdelaz3r

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.

Post #7

Last Post!

Kurisu

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.

Post #9