Aggregates with resource relationships

alecstewart1
2023-06-06

alecstewart1:

Hello!

So maybe there’s something I’m not quite understanding from the documentation, but say I have a State and City. A state can have many cities, so:

relationships do
  has_many :cities, MyProject.City
end

And a city belongs to a state:

relationships do
  belongs_to :state, MyProject.State
end

Now for a state, say I want an aggregate for the number of cities in the state. Do I have an aggregates block like the following?

aggregates do
  count :number_of_cities, :cities do
    filterable? true
  end
end

Also, could this be a oneliner as just count :number_of_cities, :cities ? No do ... end ?

Thanks!

frankdugan3:

Yes, this should do what you want:

aggregates do
  count :number_of_cities, :cities
end

The do block is an alternative syntax that is sometimes required for passing more complicated options.

frankdugan3:

filterable? is true by default.

alecstewart1:

Okay, I didn’t quite know if when it said

alecstewart1:

The relationship or relationship path to use for the aggregate

alecstewart1:

That would also include fields for actual relationships between resources.

frankdugan3:

In the case of count, you generally don’t need to refer to a specific field. But in the case of say a first or sum aggregate, you often want a specific field.

frankdugan3:

For example, sum :total_population, :cities, :population

alecstewart1:

So for list, if the default field is the primary ID

alecstewart1:

The field to aggregate. Defaults to the first field in the primary key of the resource

frankdugan3:

The path can also traverse multiple relationships deep, which is very convenient.

alecstewart1:

Do I need to just have list :cities_in_state, :cities, :id ?

frankdugan3:

I’ve actully never used the list aggregate, so I’m not familair w/ the API on that one. 😅

frankdugan3:

Looking at the docs, it looks like the 3rd argument is the field, yes.

frankdugan3:

list [name], [relationship], [field]

alecstewart1:

Well I’ll try that and see if works. How exactly do aggregates get created and stored? So a state could have like 20000 cities, so if I call for that aggregate somewhere with Ash am I going to have to wait for that aggregate to be initialized the first time?

alecstewart1:

I guess that’s more of a question about Postgres than Ash…

frankdugan3:

Sorry, missed your last question. It will return whichever field (id by default). So if you wanted a different field (like :label ), you’d just need to specify it.

alecstewart1:

Oh okay. Well it says that field is required but it defaults to the primary ID so I was confused as to whether or not it’s actually required.

frankdugan3:

Yeah, that is more of a PG specific question, and I’d assume for something that large, you would want an index to increase efficiency.

frankdugan3:

You can omit it if :id is OK.

alecstewart1:

Well looks like I found what I might need. https://ash-hq.org/docs/dsl/ash-resource#postgres-custom_indexes

alecstewart1:

Also I guess there’s always the solution of creating materialized views with SQL in the migrations.

frankdugan3:

Yeah, I’m using a materialzed view for a report or two and have an Oban job that refreshes it regularly and on demand.

alecstewart1:

Oh shoot. I forgot about the Oban library. That could fit our use cases pretty well if we don’t feel like having cron jobs that run mix .