Memoirs of a Roadie

I had an SQL query I wanted to translate into a DBIx::Class statement. I knew there must be a way, but trying to find the answer took some time. As a result I though it worth sharing in the event somebody else might be trying to find a similar answer.

The SQL I was trying to convert was:

SELECT status,count(*) AS mailboxes,
count(distinct username) AS customers
FROM mailbox_password_email GROUP BY status

The result I got running this by hand gave me:

+-----------+-----------+-----------+
| status    | mailboxes | customers |
+-----------+-----------+-----------+
| active    |     92508 |     48791 |
| completed |       201 |       174 |
| inactive  |    116501 |     56843 |
| locked    |    129344 |     61220 |
| pending   |      1004 |       633 |
+-----------+-----------+-----------+

My first attempt was:

my @rows = $schema->resultset('Mailboxes')->search({},
    {
         group_by => 'status',
         distinct => 1,
         '+select' => [
            { count => 'id', -as => 'mailboxes' }, 
            { count => 'username', -as => 'customers' } ]
    })->all;

Unfortunately this gave me the following error:

DBIx::Class::ResultSet::all(): Useless use of distinct on a grouped 
resultset ('distinct' is ignored when a 'group_by' is present) at 
myscript.pl line 469

So I took the 'distinct  => 1' out and got the following results:

+-----------+-----------+-----------+
| status    | mailboxes | customers |
+-----------+-----------+-----------+
| active    |     92508 |     92508 |
| completed |       201 |       201 |
| inactive  |    116501 |    116501 |
| locked    |    129344 |    129344 |
| pending   |      1004 |      1004 |
+-----------+-----------+-----------+

Which might be distinct for the mailboxes, but is not sadly distinct for customers. So I try:

my @rows = $schema->resultset('Mailboxes')->search({},
    {        
        group_by  => 'status',
        '+select' => [ 
            { count => 'id', -as => 'mailboxes' },
            { count => 'username', -as => 'customers', distinct  => 1 } ]
    })->all;

and get:

Failed to retrieve mailbox password email totals: 
DBIx::Class::ResultSet::all(): Malformed select argument - too many keys
 in hash: -as,count,distinct at myscript.pl line 469\n

After several attempts at Google, and reading the DBIx::Class::Manual, I finally stumbled on: SELECT COUNT(DISTINCT colname)

My query now looks like:

my @rows = $schema->resultset('Mailboxes')->search({},
    {
        group_by  => 'status',
        '+select' => [
            { count => 'id', -as => 'mailboxes' }, 
            { count => { distinct => 'username' }, -as => 'customers' } ]
    })->all;

And provides the following results:

+-----------+-----------+-----------+
| status    | mailboxes | customers |
+-----------+-----------+-----------+
| active    |     92508 |     48791 |
| completed |       201 |       174 |
| inactive  |    116501 |     56843 |
| locked    |    129344 |     61220 |
| pending   |      1004 |       633 |
+-----------+-----------+-----------+

Exactly what I was after.

DBIx::Class does require some head-scratching at times, but looking at  the final statement it now seems obvious, and pretty much maps directly  to my original SQL!

Hopefully, this provides a lesson others can find  and learn from.