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
end

Aggregates 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)
end

Calculations 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
end

One-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
end

Parent-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
end

Aggregate 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
end

For 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
end

Multi-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
end

Then use that implementation from a resource aggregate.

aggregates do
  custom :ticket_subjects_joined, :tickets, :string do
    implementation {MyApp.StringAgg, field: :subject, delimiter: ", "}
  end
end

AshSqlite.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 first and list aggregate sorts

Unsupported Cases

Full aggregate parity with AshPostgres is not available. Unsupported cases include:

  • root list and custom query aggregates through Ash.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 first and list aggregates
  • uniq list aggregates sorted by fields other than the listed field
  • fanout-prone sum, avg, list, custom, or field-based count aggregate 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.