Add group_by to Query

[JaapRood]

I come along alot of scenarios where I want to group rows in order to find the "last updated" record. I know this can be achieved in relations by limiting it to 1 result and ordering the right way, but when there is a relation to external data (say, resources retrieved from a 3rd party) group by functionality would help alot. It's the only way I can imagine to get that one record per relation identifier without having to do a separate query for every single external id, or to process your result set afterwards.

To clarify, this is what you sorta do now (untested example)

$external_ids = array('3442,'5113','3193');

$results = array();
foreach ($external_ids as $ex_id) {
    $result[] = Model_Entry::find()
        ->where('ex_id', $ex_id)
        ->order_by('updated_at', 'desc')
        ->limit(1);
}

or this (untested example)

$external_ids = array('3442,'5113','3193');

$tmp_results = Model_Entry::find()
    ->where('ex_id', 'IN', $external_ids)
    ->order_by('updated_at', 'desc');

$results = array();
foreach ($tmp_results as $t_result) {
    if (count($external_ids) <= count($results)) break;
    if (array_key_exists($t_result->ex_id, $results)) continue;
    $results[$t_result->ex_id] = $t_result;
}

Allthough, with group by functionality, in my mind, it could look as simple and elegant as this

$external_ids = array('3442,'5113','3193');

$results = Model_Entry::find()
    ->where('ex_id', 'IN', $external_ids)
    ->order_by('updated_at', 'desc')
    ->group_by('ex_id');

I don't know if grouping fits in the Active Record pattern, as the resulting objects would not represent a single row, but an aggregration of multiple. However, this code could prove very useful in certain cases.

[jschreuder]

Postponing this again, open to pull-requests though.

[billmn]

@JaapRood can you send a pull request about your implementation?

[JaapRood]

I wish I could! I actually never got around to implementing it, didn't have the resources (read: time) to invest that code back then.