AshSqlite supports resource aggregates that can be loaded, filtered, sorted, and used in expression calculations. For general Ash aggregate usage, see the Ash aggregates guide.
Supported Aggregates
AshSqlite supports related count, sum, avg, min, max, exists, first, list, and custom aggregates over normal relationship paths.
aggregates do
count :total_tickets, :tickets
exists :has_open_tickets, :tickets do
filter expr(status == :open)
end
first :first_ticket_subject, :tickets, :subject do
sort subject: :asc_nils_last
end
list :ticket_subjects, :tickets, :subject do
sort subject: :asc_nils_last
end
endAggregates are translated to SQL and can be used in queries.
require Ash.Query
Helpdesk.Support.Representative
|> Ash.Query.filter(total_tickets > 2)
|> Ash.Query.sort(total_tickets: :desc)
|> Ash.Query.load([:total_tickets, :first_ticket_subject])
|> Ash.read!()Aggregates can also be loaded on records that have already been read.
representatives = Helpdesk.Support.read!(Helpdesk.Support.Representative)
Ash.load!(representatives, [:total_tickets, :ticket_subjects])Loaded query aggregates may use string names. Their values retain those names
in the record's aggregates map. Aggregate references through relationships,
such as comments.count_of_ratings, are evaluated against the related record.
Expression calculations can also use inline related aggregates, including
list(posts, field: :title).
Query Aggregates
AshSqlite supports root query aggregates for count, sum, avg, min, max,
first, and exists.
Helpdesk.Support.Representative
|> Ash.Query.filter(active == true)
|> Ash.aggregate!(count: :count)Relationship query aggregates are not supported by the grouped aggregate query path. Define a resource aggregate and load, filter, or sort on that aggregate instead.
Root query aggregate limits and offsets are applied before aggregation. A
fieldless count with uniq?: true counts the resource's primary key and
requires the resource to have exactly one primary-key attribute.
Calculations
Expression calculations can reference aggregates and be pushed down to SQLite.
aggregates do
count :total_tickets, :tickets
count :open_tickets, :tickets do
filter expr(status == :open)
end
end
calculations do
calculate :percent_open, :float, expr(open_tickets / total_tickets)
endCalculations that reference aggregates can be loaded, filtered, and sorted in the same way.
require Ash.Query
Helpdesk.Support.Representative
|> Ash.Query.filter(percent_open > 0.25)
|> Ash.Query.sort(:percent_open)
|> Ash.Query.load(:percent_open)
|> Ash.read!()Relationship Paths
Aggregates are supported over normal relationship paths, including multi-hop paths.
aggregates do
count :comment_count, [:posts, :comments]
sum :paid_total, [:orders, :payments], :amount
endOne-hop many-to-many relationship aggregates are supported. Scalar aggregates are also supported when a multi-hop path ends in a many-to-many relationship.
aggregates do
count :linked_post_count, :linked_posts
count :linked_post_count_through_posts, [:posts, :linked_posts]
first :first_linked_post_title, :linked_posts, :title do
sort title: :asc_nils_last
end
endParent-independent unrelated aggregates are supported when the aggregate query does not need values from the parent row.
aggregates do
count :published_post_count, Post do
filter expr(published == true)
end
endAggregate Filters
Aggregate filters and aggregate join_filters are supported for normal paths and one-hop many-to-many paths when they do not depend on parent row values.
aggregates do
count :open_ticket_count, :tickets do
filter expr(status == :open)
end
count :matching_ticket_count, :tickets do
join_filter :tickets, expr(priority == :high)
end
endFor many-to-many aggregates, a join_filter on the many-to-many relationship applies to the destination resource side of the aggregate. Put through-resource filtering on the relationship's configured join relationship/filter.
For filters that need to test a to-many relationship without multiplying the aggregate rows, prefer exists/2.
aggregates do
sum :liked_comment_total, :comments, :likes do
filter expr(exists(ratings, score > 5))
end
endMulti-hop aggregates use each relationship's configured read action. If an intermediate hop needs scoped rows, define the read action on that relationship rather than trying to override it per aggregate.
Intermediate relationships and many-to-many join resources retain their read
action arguments, shared context, and attribute-based tenant scope. Read action
filters apply before relationship limits and offsets. Aggregate filters apply
to the resulting bounded relationship, including for first and list.
SQLite Requirements
Aggregate filters and first/list aggregate ordering require SQLite 3.30.0 or later. Window functions were added in SQLite 3.25.0, but AshSqlite's generated SQL also uses aggregate FILTER clauses and explicit NULLS FIRST/NULLS LAST ordering, which require SQLite 3.30.0 or later. list aggregates also require JSON functions.
- window functions
- aggregate
FILTER - JSON aggregation
- explicit null ordering
JSON functions are built into SQLite by default as of SQLite 3.38.0. Older SQLite builds need the JSON1 extension enabled. Check the SQLite library used by your application, which may not be the same binary as the sqlite3 command:
MyApp.Repo.query!("select sqlite_version()")
MyApp.Repo.query!("select json_group_array(1)")list aggregates return lists through SQLite JSON aggregation. custom aggregates require a SQLite-compatible aggregate expression or function.
Custom Aggregates
Custom aggregates should use both Ash.Resource.Aggregate.CustomAggregate and AshSqlite.CustomAggregate.
defmodule MyApp.StringAgg do
use Ash.Resource.Aggregate.CustomAggregate
use AshSqlite.CustomAggregate
require Ecto.Query
def dynamic(opts, binding) do
Ecto.Query.dynamic(
[],
fragment("group_concat(?, ?)", field(as(^binding), ^opts[:field]), ^opts[:delimiter])
)
end
endThen use that implementation from a resource aggregate.
aggregates do
custom :ticket_subjects_joined, :tickets, :string do
implementation {MyApp.StringAgg, field: :subject, delimiter: ", "}
end
endAshSqlite.CustomAggregate only defines the dynamic/2 contract. Custom aggregates must return SQLite-compatible Ecto dynamic expressions. This behaviour does not install SQLite extensions or register user-defined functions. If your custom aggregate uses a function that is not built into SQLite, register it with the SQLite connection yourself and make sure it is available in every environment.
Performance
AshSqlite builds aggregate queries as grouped subqueries or windowed subqueries and joins those results back to the parent query. Add indexes for the relationship keys used by those subqueries.
Useful indexes usually include:
- child foreign keys, like
tickets.representative_id - many-to-many join resource key pairs
- fields used by aggregate filters
- fields used by
firstandlistaggregate sorts
Unsupported Cases
Full aggregate parity with AshPostgres is not available. Unsupported cases include:
- root
listandcustomquery aggregates throughAsh.aggregate/2 - relationship query aggregates
- unrelated aggregates that reference the parent row
- manual relationships
no_attributes?relationships- multi-hop paths that include many-to-many relationships before the final hop
- non-scalar aggregates over multi-hop paths that include many-to-many relationships
- parent-dependent relationship filters
- parent-dependent aggregate filters
- parent-dependent
join_filters - aggregate filters that reference other aggregates
- expression sorts on
firstandlistaggregates uniqlist aggregates sorted by fields other than the listed field- fanout-prone
sum,avg,list,custom, or field-basedcountaggregate filters over to-many relationship references
A fanout-prone aggregate filter is one where filtering joins another to-many relationship and can duplicate the rows being aggregated. For example, a sum of comment likes filtered by popular_ratings.id could count the same comment once per matching rating. AshSqlite rejects these shapes instead of returning an over-counted result. Use exists/2 when you only need to test that related rows exist.