[CDBI] GROUP_CONCAT and user-defined aggregates

I often need to display a list of widgets where each widget might have
multiple related attributes (many colors, sizes) that are represented
in link tables.

CDBI makes it easy since you can do something like:

    for my $widget ( @widget_list ) {
        print $widget->name;
        for my $color ( $widget->colors ) {
            print $color;
        }
    }

But, of course, that's a lot of hits on the database.  What I
typically do is gather up all the widget ids and then do just one
extra select to get all the colors.

MySQL has the GROUP_CONCAT feature which can reduce that to a single
query (returning the associated rows in a comma-separated list).

I'm curious if anyone is using GROUP_CONCAT with CDBI and in a way
such that in the above code:

        for my $color ( $widget->colors ) {
            print $color;
        }

would not hit the data base again -- rather just build the objects
based on the GROUP_CONCAT column.

I use Postgresql, not MySQL.  It doesn't have GROUP_CONCAT, but does
have user-defined aggregates, which I think can do the same thing.
Anyone using that Postgresql feature?


Yes, I can just use a join to get all the associated items in a single
query.  But, the join will result in the same widget returned in more
than one row.  And that throws off my LIMIT and OFFSET page
calculations when I only want to show a widget once.




-- 
Bill Moseley
[email protected]