[Dbix-class] Distinct, order_by and group_by

[Bill Crawford]

On 21 December 2010 10:43, Carl Vincent <c.a.vincent at newcastle.ac.uk> 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.

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.