[Dbix-class] DISTINCT group by sql

[Matt S Trout]

Chris Lock wrote:
> Hello everyone,
> 
> So Im doing a fairly complicated select on a DB I have, I need to group
> the query by the fields specified in the group by below
> 
> my $results = $c->model('BrandingDB::Details')->search_literal(
>         'datetime >= ? AND datetime < ? AND campaign_id = ?',
>         ( $date_hash->{start_date}, $date_hash->{end_date},
> $c->session->{campaign_id} ),
>         {
>             select => [
>                 { DATE_FORMAT => [
>                     { DATE => 'datetime' },
>                     '"%d-%m-%Y"'
>                 ] },
>                 'channel',
>                 { FORMAT => [
>                     { SUM => 'clicks' },
>                     0
>                 ] },
>                 { FORMAT => [
>                     { SUM => 'impressions' },
>                     0
>                 ] },
>                 { FORMAT => [
>                     { SUM => 'clicks/impressions' },
>                     2
>                 ] }
>             ],
>             as       => [qw/ date channel clicks impressions ctr /],
>             group_by => [ { DATE => 'datetime' }, 'channel' ],
>             rows     => 25,
>             page     => $page
>         }
>     );
> 
> which makes this SQL
> 
> SELECT DATE_FORMAT( DATE( datetime ), "%d-%m-%Y" ), channel, FORMAT(
> SUM( clicks ), 0 ), FORMAT( SUM( impressions ), 0 ), FORMAT( SUM(
> clicks/impressions ), 2 ) FROM details me WHERE ( datetime >= ? AND
> datetime < ? AND campaign_id = ? ) GROUP BY DATE( datetime ), channel
> LIMIT 25 (`2006-5-1', `2006-5-31', `1')
> 
> I get results from that no porblem, but try to call a pager on that
> ($results->pager) or count for that matter this bit of SQL gets passed
> tol the DB and happily spat back at me
> 
> SELECT COUNT( DISTINCT( DATE( datetime ), channel ) ) FROM details me
> WHERE ( datetime >= ? AND datetime < ? AND campaign_id = ? )
> (`2006-5-1', `2006-5-31', `1')
> 
> With the following SQL error
> Operand should contain 1 column(s)
> 
> After a bit of messing about, the good bit of SQL it should produce is
> SELECT COUNT( DISTINCT( DATE( datetime ) ), channel ) FROM details me
> WHERE ( datetime >= '2006-5-1' AND datetime < '2006-5-31' AND
> campaign_id = 1 )

If the datetime is sufficient as an argument to the DISTINCT clause, why do 
you need both in the GROUP BY?

-- 
      Matt S Trout       Offering custom development, consultancy and support
   Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

+ Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +