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)
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[EMAIL PROTECTED]
