[Dbix-class] Problem with getting a grouped count

[Matija Grabnar]

On Sun, Mar 19, 2006 at 09:50:52PM +0000, Matt S Trout wrote:
> Matija Grabnar wrote:
> > I've been trying to follow the example made in the cookbook, but for some reason, the "as"=> part doesn't seem
> > to be working:
> > 
> > my $qs=$schema->resultset('Schema::Msgs')->search({},
> >      {select   => [ 'quar_type',{ count => '*' } ],
> >       join     => [ qw/quarantined/],
> >       as       => [ qw/quar_type qcount/],
> >       group_by => [ qw/quar_type/],
> >      });
> > 
> > Here is the actualy query that gets executed:
> > 
> > SELECT quar_type, COUNT( * ) FROM msgs me LEFT JOIN quarantine quarantined ON ( quarantined.mail_id = me.mail_id ) GROUP BY quar_type
> > 
> > The problem is that since the as part doesn't execute, I can't do an order_by /qcount/, and what's even
> > worse, I can't get to the count!
> 
> You *can* get to the count. DBIC will populate a 'qcount' column in the 
> inflated object, so
> 
> $obj->get_column('qcount')
> 
> will work fine.
> 
> As noted in the ResultSet docs
> 
>         as
> 
>         Arguments: (arrayref)
> 
>         Indicates column names for object inflation. This is used
>         in conjunction with "select", usually when "select" con?
>         tains one or more function or stored procedure names:
> 
> the 'as' attribute does *not* translate to SQL. It's for the DBIC stuff only.
> 

Thank you!

I just tested $obj->get_column('qcount') and it indeed does get me
the column I need. 

Any idea what I'd have to do to be able to sort by the count, too?
I've been looking at the SQL::Abstract manpage but I can't see how to
convince it to assign an alias to the count, even the literal sql
doesn't seem to be able to do it.