[Dbix-class] Distinct, order_by and group_by

[Carl Vincent]

>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.

>From: Bill Crawford [mailto:billcrawford1970 at gmail.com]
>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