[Dbix-class] best practices for sub-grouping results?

[Jason Gottshall]

Given the typical CD/Artist example schema:

Artist->has_many(cds)
Genre->has_many(cds)
CD->belongs_to(artist)
CD->belongs_to(genre)
CD->add_column(release_year)

I have a CD search page, where the user may wish to group (categorize)
the results under sub-headings by artist, genre, or release year. It
seems I should be able to use a single template for this, given a data
structure (or object?) over which it can iterate in two nested loops;
outer loop for selected category, inner loop for results in that
category. But what should that data structure/object look like?

If I start with a CD resultset, based on the search criteria, I can
easily get a new rs for the category headings if their based on a
relationship:

$cd_rs = $schema->resultset('CD')->search(...);
$categorizer = 'artist'; # or $c->req->param('grouped_by');
$cat_rs = $cd_rs->search_related($categorizer);

But as I loop over that rs, how do I enuerate just the members of the
category?

[% WHILE (cat = cat_rs.next %]
  <h2>[% cat.name %]</h2>
  <ul>
  [% FOREACH cd IN cat.cds %]
    <li>[% cd.title %]</li>
  [% END %]
[% END %]

That will list every CD in each category, not just the ones that matched
the original search criteria. And I don't want to have to resort to:

  [% FOREACH cd IN cd_rs.search{{ "me.${categorizer}_id" => cat.id }) %]

That's too much exposure of the model API in the template, plus it seems
inefficient. And of course, this technique won't work at all if the user
wants to group by a column that isn't a foreign key, like release_year.
There's no way to get an rs for the categories to begin with.

Has anyone else found an elegant/efficient solution to this problem?
I've been toying with the idea of creating some sort of DBIC::RSGrouped
component to encapsulate this scenario, but I don't want to trudge too
far down that road without some advice from the community!

--Jason
jgottshall at capwiz.com