>On 21 December 2010 10:43, Carl Vincent <[email protected]> >wrote: >> 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.
>From: Bill Crawford [mailto:[email protected]] >Sent: 21 December 2010 11:03 > >You probably want to limit the columns you select, and/or add a >group_by on the two date columns; and order_by => [ qw/year month/ ] >(I /think/ that works, or you might have to spell out the >DATE_FORMAT(...) in there). > >Otherwise the DISTINCT is over the whole row, not just those two >columns you want it on. Hi Bill, Limiting the columns, doesn't affect the resulting query: { columns => [ { year => 'DATE_FORMAT(release_date,"%Y")'} , { month => 'DATE_FORMAT(release_date,"%m")' },], distinct => 1, order_by => [ 'release_date DESC' ], }; But specifying the order_by in terms of those columns (aliases don't seem to work) does get the result I'm after: { columns => [ { year => 'DATE_FORMAT(release_date,"%Y")'} , { month => 'DATE_FORMAT(release_date,"%m")' },], distinct => 1, order_by => [ 'DATE_FORMAT(release_date,"%Y") DESC','DATE_FORMAT(release_date,"%m") DESC' ], }; 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") ORDER BY DATE_FORMAT(release_date,"%Y") DESC, DATE_FORMAT(release_date,"%m") DESC It now works as I need, but I can't help thinking it must be possible to jump through less hoops! Thanks for your help. 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]
