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

[Darren Duncan]

Jason Gottshall wrote:
> 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!

How I would solve that problem is with an analogy to SQL's GROUP BY that 
returns the grouped rows as groups of rows rather than just the results of 
performing aggregate functions on said rows.

For example, given a table like this:

MYTBL

COL1|COL2
----+----
a   |   1
----+----
a   |   2
----+----
a   |   3
----+----
b   |   4
----+----
b   |   5

I could say in quasi-SQL:

SELECT COL1, COL3 FROM MYTBL GROUP (COL2) AS COL3

and get:

COL1|COL3
----+-----
a   |+----
     ||COL2
     |+----
     ||   1
     |+----
     ||   2
     |+----
     ||   3
     |+----
----+-----
b   |+----
     ||COL2
     |+----
     ||   4
     |+----
     ||   5
     |+----

That result rendered in Perl as per DBI's array-of-hashrefs would look like:

[
   { COL1 => 'a',
     COL3 => [
       { COL2 => 1 },
       { COL2 => 2 },
       { COL2 => 3 },
     ],
   },
   { COL1 => 'b',
     COL3 => [
       { COL2 => 4 },
       { COL2 => 5 },
     ],
   },
]

And you could then iterate through that in TT or what have you without 
exposing your model too badly, because that structure came out of a single 
query, not multiple queries that had to be interleaved.

Therefore, perhaps how you would solve the problem with DBIx::Class is to 
make its interface work as if SQL supported what I demonstrated, and then 
do things naturally as a single search query.  Eg if you were to make an 
extension, I suggest making it provide that feature.

-- Darren Duncan

P.S.  The feature I demonstrated also has the advantage of being lossless; 
you could get the original rowset back from the grouped one like this (the 
subquery is the original, the outer query is the inverse operation):

SELECT COL1, COL2 FROM (
   SELECT COL1, COL3 FROM MYTBL GROUP (COL2) AS COL3
) UNGROUP COL3 AS (COL2)