Add support for query set annotations

[ellmetha]

Description

Presently, the Marten ORM allows to perform basic aggregation operations and get the result of these operations right away (for example through the use of the #minimum, #maximum, #average, #sum, and #count methods). That being said it is not possible to "keep" the result of such operations in order to then make use of them as part of the initial query set. For example, it is not possible to query for Book records and order them by the number of associated Author records.

Proposition

Let's make it possible to define query sets with aggregation operations that should be performed at the SQL level. These annotations should be configurable through the use of a new #annotate method that would provide a convenient DSL for defining annotations in a block.

For example:

# Implicit annotation - generates an "authors__count" annotation
Book.all.annotate { count(:authors) }

# Explicit annotation - generates a "my_count" annotation
Book.all.annotate { count(:authors, as: :my_count) }.order(:my_count)

# Setup multiple annotations at once
Book.all.annotate do
  count(:authors, as: :author_count)
  sum(:pages, as: :total_pages)
end

Unlike the existing aggregation methods (#minimum, #maximum, #average, #sum, and #count), the use of #annotate should return a new query set object that can then be further filtered or sorted (by leveraging the configured annotation aliases).

Supported aggregations

For now, we should aim at supporting the aggregations that we already support through the use of the #minimum, #maximum, #average #sum, and #count query set methods:

Book.all.annotate { count(:authors) }
Author.all.annotate { minimum(:books__price) }
Author.all.annotate { maximum(:books__price) }
Author.all.annotate { average(:books__price) }
Author.all.annotate { sum(:books__price) }

Implicit and explicit aliases

As highlighted in the above examples, it should be possible to infer the alias of annotations that don't explicitly define one through the use of the as argument:

# Implicit annotation alias
Book.all.annotate { count(:authors) }

# Explicit annotation alias
Book.all.annotate { count(:authors, as: :my_count) }.order(:my_count)

Distinct annotations

The count, sum, and average aggregations should also support using a special distinct argument allowing to specify that the aggregation must be performed on unique values:

Book.all.annotate { count(:authors, distinct: true) }

Accessing annotation results on records

It should be possible to access the result of aggregations on the records returned by query sets containing annotations. These result values should be made available in a hash, accessible through the use of a new #annotations method:

books = Book.all.annotate { count(:authors, as: :authors_count) }
books[0].annotations[:authors_count] # => 3