[Dbix-class] Using Database Functions

[matthew couchman (JIC)]

Ah, yes that's done the trick. Thanks very much for your help Eden.

> -----Original Message-----
> From: Eden Cardim [mailto:edencardim at gmail.com]
> Sent: 15 September 2009 05:05
> To: DBIx::Class user and developer list
> Subject: Re: [Dbix-class] Using Database Functions
> 
> 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/
> 
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-
> class at lists.scsys.co.uk