[Dbix-class] Re: [Catalyst] Migration from Class::DBI to DBIx::Class (with CDBICompat)

[Brandon Black]

On 1/11/06, Matt S Trout <dbix-class at trout.me.uk> wrote:
> On Wed, Jan 11, 2006 at 03:23:36PM -0600, Brandon Black wrote:
> > On 1/10/06, Matt S Trout <dbix-class at trout.me.uk> wrote:
> > > I haven't seen the set_sql yet I couldn't persuade ResultSet to build for me :)
> >
> > Now there's a bold challenge :) ... we still need code for DISTINCT,
> A "weird SQL SELECTs gallery" (along the lines of the joins gallery I used for
> inspiration on 0.03) would be appreciated.
>

Here's one for you.  I tried switching some of my code over to the
branch today.  There's a construct I've been evilly using (since it
relies on non-public interfaces) to get aggregate functions.  It works
in stable, breaks on the branch:

sub get_min_thing {
    my $thing_ids_arrayref = shift;

    my @cols = $things_table_class->search(
        { thing_id => $thing_ids_arrayref },
        { cols => [ 'min(thing_col)' ] }
    );

    return $cols[0]->{_column_data}->{'min(thing_col)'};
}

I would suspect the appropriate answer is to have a new Attribute
similar to cols, which knows about aggregation and plays nicely with
group_by.  IIRC, the general rule is that if you're going to do any
aggregating functions at all on the results of a SELECT query, then
every column must either be aggregated, or be part of the group_by.

Perhaps an interface like:

$things_table_class->search(
        { thing_id => $thing_ids_arrayref },
        { aggregate_cols => [ { 'thing_col' => 'min' },
                                       { 'thing_col' => 'max' },
                                       { 'thing_col2' => 'min' },
                                     ],
          group_by => 'xxx',
        }
);

Then the searching method could validate a few things (like, you
cannot have a "cols" if you have an "aggregate_cols", and then
transform this into:

SELECT min(thing_col), max(thing_col), min(thing_col2), xxx FROM $tbl
WHERE thing_id IN (...) GROUP BY xxx

The big question is, exactly how does ResultSet show us these results?
 Since the same column can be aggregated multiple times, and the same
agg function can be used multiple times, you can only define the
resulting pseudo-cols uniquely by using both parts.  Should there be a
seperate ->get_aggregated_column('min', 'thing_col') ?

-- Brandon