Aggregate count of nested relation with filtering at multiple levels

Ash Forum Ash Questions / Help
,
1

I have resources D → C → B → A, belongs_to all the way down, and postgres w/ context multitenancy.

I want to do a count of D grouped by A, and I need to filter D, C, B and A.

Something like:
select A.id, count(D)
from D join C on … join B on … join A on …
where D.status = … and C.foo = … and B.bar = …
group by A.id

Struggling to figure out the “Ash” approach for this. I don’t need it to live in a resource’s definition. Can just be a custom Ash.Query.

Thank you!

2

Do you need to be able to filter/sort on this value? Or are you just looking to load it likee a calculation on resource D? Are the filters dynamic? Like taken from input? Or are they static things like C.approved == true and ...?

3

I don’t need to filter or sort the result. Just need the values in some array of tuples or maps, e.g. [{id, count}, …]

Most of the filtering in the query will be static, except one field on B will need to be taken from input, an id field. This is for some internal reporting, so I don’t need to worry about policies in my use case.

Curious to see how you approach the answer. I saw some ecto from queries in a few ash related projects, but none that needed to deal with multitenancy, so I wasn’t sure how to use the correct postgres schema.

4

For multi tenancy it depends on the strategy, but you can usually specify those things in ecto. There isn’t a really elegant way currently to build an aggregate that provides filters for each relationship along the way. I’d like to make it more elegant, but for now it would probably make sense to write this particular bit of code using ecto directly. Your Ash resources are also ecto schemas automatically, so you should be able to do whatever you need on that front.

5

Just to close the circle, here is my solution, sans user input:

    action :my_func, {:array, :struct} do
      run fn input, context ->
        from(d in __MODULE__,
          join: c in assoc(d, :c),
          join: b in assoc(c, :b),
          join: a in assoc(b, :a),
          where:
            b.tenant_id == ^context.actor.tenant_id and
              is_nil(b.archived_at) and
              is_nil(c.archived_at) and
              c.status == ^:completed and
              is_nil(a.archived_at) and
              a.status == ^:approved,
          group_by: [a.id, b.id],
          select: [a.id, b.id, count()]
        )
        |> MyApp.Repo.all(prefix: input.tenant)
        |> Enum.map(fn [a_id, b_id, count] -> %{a_id: a_id, b_id: b_id, count: count} end)
        |> then(&{:ok, &1})
      end
    end