Hi
I've got the following snippet of code to produce a list of distinct calendar
months where an article was published:
my $rs = $db->resultset('NewsArticle');
my $args = { select => [ 'DATE_FORMAT(release_date,"%Y")',
'DATE_FORMAT(release_date,"%m")',],
as => ['year','month'],
distinct => 1,
order_by => [ release_date ],
};
my $m_rs = $rs->search({},$args);
and it's producing the following SQL:
SELECT DATE_FORMAT(release_date,"%Y"), DATE_FORMAT(release_date,"%m") FROM
NewsArticles me GROUP BY DATE_FORMAT(release_date,"%Y"),
DATE_FORMAT(release_date,"%m"), me.release_date ORDER BY release_date
It seems there's an extra GROUP BY term "me.release_date" which is not
producing the desired distinct result - I get multiple rows where there is more
than one article released in a month.
This problem appeared when we upgraded from an ancient DBIC to 0.08123.
There's an entry in the changelog for 0.08124 which I thought would be relevant:
- Fixed distinct with order_by to not double-specify the same column in
the GROUP BY clause
But an upgrade to DBIC 0.08124 and SQL::Abstract 1.71 didn't fix it.
Is this the problem referred to in the changelog, or is it something else?
Alternatively is there a better way to express my query to get the distinct
list of months?
Thanks
Carl
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Carl Vincent http://www.netskills.ac.uk/ (URL)
Systems Manager 0191 222 5003 (voice)
Netskills, Newcastle University 0191 222 5001 (fax)
Netskills is a JISC Advance service
Training - Development - Research - Innovation
_______________________________________________
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]
