Add support for SQL standard aggregate functions

[martinleffl@…]

I searched this site for "aggregate function" used in mysql (ok, used in standard sql)..but i can't find any method to make a "group by" with function "count()", "max()", "min()"...

[acidbox]

Good question, I'd like to know this too.

[synace]

Maybe you could add a way to make these functions available via propel.. but it's basically just standard sql fields via addSelectColumn and addGroupByColumn.

or, you could add a new function to generate expressions for a passed function, expression/column and modifier in /trunk/generator/classes/propel/engine/builder/om/php5/PHP5BasicPeerBuilder.php

would access with params ([AVG|COUNT|MIN|MAX|SUM], [field|expression], [ALL|DISTINCT])

PostPeer::someAggregateFunction(
    CRITERIA::SOME_CONSTANT_COUNT, 
    PostPeer::FIELD_NAME_TO_USE, 
    CRITERIA::SOME_OPTINAL_CONSTANT_FOR_ALL_OR_DISTINCT
); 

returns:

COUNT(table.field)

Other than that, the functionality is provided in the methods described above. They're in /trunk/runtime/classes/propel/util/Criteria.php

I've included example use, and list of aggregate function supported.

snip examples:

$criteria->addSelectColumn('COUNT(table2.col)');
$criteria->addGroupByColumn('table1.id');

forum hasmanyposts, post belongstoforum

ForumPeer::addSelectColumns($c);
$c->addSelectColumn(PostPeer::COUNT);
$c->addGroupByColumn(ForumPeer::ID);

SQL99  http://www.oreilly.com/catalog/sqlnut/chapter/ch04.html

AVG(expression)
Computes the average value of a column by the expression

COUNT(expression)
Counts the rows defined by the expression

COUNT(*)
Counts all rows in the specified table or view

MIN(expression)
Finds the minimum value in a column by the expression

MAX(expression)
Finds the maximum value in a column by the expression

SUM(expression)
Computes the sum of column values by the expression

Technically speaking, ANY, EVERY, and SOME are considered aggregate 
functions. However, they have been discussed as range search criteria 
since they are most often used that way. Refer to the 
SELECT . . . WHERE topic in the previous chapter for more information 
on these functions.

The number of values processed by an aggregate varies depending on the 
number of rows queried from the table. This behavior makes aggregate 
functions different from scalar functions, which require a fixed number 
and fixed type of parameters.

The general syntax of an aggregate function is:

aggregate_function_name ( [ALL | DISTINCT] expression )

[synace]

set severity to 'enhancement'

[soenke]

changed to 1.3 milestone.

[hans]

I'm cleaning up the description & summary and moving this to 2.0; maybe we can make this fit into new Criteria ...