Aggregates with resource relationships
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
.