[Dbix-class] Using Database Functions

[Eden Cardim]

On Mon, Sep 14, 2009 at 9:41 AM, matthew couchman (JIC)
<matthew.couchman at bbsrc.ac.uk> wrote:
> I’m using DBIx via Catalyst to try and create the underlying SQL query:
> SELECT DISTINCT( YEAR( date ) )  FROM gps_location me;
> Following the examples at DBIx::Class::Manual::Cookbook it looked like this
> would do the trick:
> $c->stash->{years} = $c->model('DB::GpsLocation')->search(
>                     {},
>                     {
>                                 select => [ { YEAR => 'date' } ],
>                                 as => [ 'year' ],
>                                 distinct => 1,
>                     }
> );
> However when I run this COUNT() has been added to the query:
> SELECT COUNT( DISTINCT( YEAR( date ) ) ) FROM gps_location me;
> Where does the COUNT() come from and how do I get rid of it?

You're attempting to stringify $c->stash->{years} somewhere,
ResultSets stringify as ->count, so that's where you're getting the
COUNT in your query from.

You probably want to create and iterate through a ResultSetColumn instead:

my $years = $rs->search({}, { select => [ { YEAR => 'date' }], as =>
['year'], distinct => 1 });
my $year_cols = $years->get_column('year'); # returns a
DBIx::Class::ResultSetColumn
while(my $year = $year->next) { do_something($year) }

-- 
   Eden Cardim       Need help with your Catalyst or DBIx::Class project?
  Code Monkey                    http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Want a managed development or deployment platform?
http://edenc.vox.com/            http://www.shadowcat.co.uk/servers/