[Dbix-class] Distinct, order_by and group_by

[Bill Crawford]

On 21 December 2010 12:29, Carl Vincent <c.a.vincent at newcastle.ac.uk> wrote:

> 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' ],
> };

Having the order_by means that column has to be included in the query,
and that mucks up the intent of the "distinct". But that's just SQL;
it's not always intuitive, but there's method to its madness :-D

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

Well, it's only the two hoops :)

> Thanks for your help.

You're very welcome. Christmas cheer and me being all happy at the moment :)

> Carl

Will