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

[matthew couchman (JIC)]

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?

Thanks very much,

                                Matt.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20091124/17211bfe/attachment.htm