[Dbix-class] DISTINCT group by sql

[Chris Lock]

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 )

Any idea on how I can get the pager to work?

- Chris