[Dbix-class] Problem selecting distinct years from date column

[matthew couchman (JIC)]

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:rabbit+dbic at rabbit.us]
> 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-
> class at lists.scsys.co.uk