Hi,
I came back to this problem and I think I've got it sorted now so I thought it
might be useful to post the solution in case anybody else was having the same
problem. My problem was that quotes were being added to the zero argument. So
the revised query looks like this:
my $years_rs = $c->model($table_name)->search(
{
date => { '!=', \"0" }
},
{
select => [ \'YEAR(date) AS year' ],
as => [ 'year' ],
group_by => [ 'year' ]
}
);
Matt.
> -----Original Message-----
> From: Peter Rabbitson [mailto:[email protected]]
> Sent: 28 November 2009 15:58
> To: Class user and developer list
> Subject: Re: [Dbix-class] Problem selecting distinct years from date
> column
>
> matthew couchman (JIC) wrote:
> > Hi,
> >
> >
> >
> > I have a table with a date column from which I’m trying to select a
> list
> > of distinct years which aren’t zero. I use this block:
> >
> >
> >
> > my $years_rs = $schema->resultset('Reading')->search(
> >
> > {
> >
> > -and => [
> >
> > date => { '!=' => 0 },
> >
> > date => { '!=' => undef }
> >
> > ]
> >
> > },
> >
> > {
> >
> > select => [ { YEAR => 'date' }],
> >
> > as => [ 'year' ],
> >
> > distinct => 1
> >
> > }
> >
> > );
> >
> > my @years = $years_rs->get_column('year')->all;
> >
> >
> >
> > I’ve set DBIX_CLASS_STORAGE_DBI_DEBUG=1 and when I run the above code
> it
> > reports:
> >
> >
> >
> > SELECT YEAR( date ) FROM colour_reading me WHERE ( ( date != ? AND
> date
> > IS NOT NULL ) ) GROUP BY YEAR( date ): '0'
> >
> >
> >
> > which looks right but when I inspect @years it contains 0, 2008, 2009
> so
> > the WHERE condition hasn’t worked. If I cut and paste the query to
> MySQL
> > Query Browser, substitute the ‘?’ for the ‘0’ and run on the same
> server
> > then I get the correct result i.e. 2008, 2009.
> >
> >
> >
> > I’m puzzled, can anybody help?
> >
>
> No idea - it works here:
> http://dev.catalyst.perl.org/svnweb/bast/revision?rev=7976
>
> Please try against your mysql server.
>
>
> _______________________________________________
> 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-
> [email protected]
_______________________________________________
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/[email protected]
