Support SUM and AVG/MAX/MIN

[arraypress]

This might be off the mark with BerlinDB direction, but It would be useful to have the ability to "SUM" specific columns in the database tables without having to write custom queries as a fallback. Having the ability to use AVG/MAX/MIN would also be beneficial in a lot of use cases.

[alexstandiford]

I think this would be useful, too. I currently have to write manual queries when I decide to do this sort-of thing.

A pattern that has worked fairly well with MongoDB has been the distinction between a query and an aggregation. I think Berlin does a great job with query, via the current Query class, perhaps it's time to consider something handles aggregations?

Theoretically, the query class could eventually turn into a wrapper for the core class that creates clauses.

I sort-of started that process in this PR, but I'm not convinced what I did should even be in the Query class.

[alexstandiford]

Some more good news - it appears that BerlinDB already has support for group_by in Query, although as of this writing it is undocumented. Probably just an omission.

<?php

$query = new Query(['groupby' => 'column_name', 'fields' => 'column_name']);

var_dump($query->items);

?>

The above would yield an array of all unique values in the column_name column of the queried table.

This will most-likely work similar to how Date works - as an independent factory class that handles all of the parsing specific to this type of query.

[arraypress]

For anyone interested, I actually built a library (class) for this here:

https://github.com/arraypress/edd-extended-query

Syntax is like this:

$query = new Task_Transactions([
    'function' => 'SUM',
    'fields'   => ['amount', 'discount'],
    'operator' => '-' // Subtracting discount from amount
]);

$total_profit = $query->get_result();

It adds aggregate functionality by hijacking the COUNT queries to perform different operations in a single query. GROUPBY queries are also supported and work as expected.

Class can be used with any BerlinDB project.