[Dbix-class] Distinct, order_by and group_by

[Carl Vincent]

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