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

[Matt S Trout]

On Mon, Jan 16, 2006 at 01:06:12PM -0600, Brandon Black wrote:
> 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)'};
> }

my $rs = $schema->resultset('Things')->search($cond,
           { select => [ { 'min' => 'thing_col' } ], as => [ 'min' ] });
return ($rs->cursor->next)[0];
 
> 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',
>         }
> );

How about

->search($cond, { select => [ { 'min' => 'thing_col' },
                              { 'max' => 'thing_col' },
                              { 'min' => 'thing_col2' } ],
                   as => [ 'min_thing', 'max_thing', 'min_thing2' ] });

which is currently implemented in the branch.
 
> 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 above ->search will produce this output.

> 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') ?

as allows you to name them, which I think obviates this issue. Anyway, that
would nicely spanner any attempt by people to implement their own result
classes and/or inflate to arbitrary classes, which would just be no fun :)

-- 
     Matt S Trout       Offering custom development, consultancy and support
  Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

 + Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +