Note: due to positive feedback on the post and at a client, I've released DBIx::Class::Report to the CPAN. You can read the original announcement here.

An offhand tweet about Perl 6, inspired by this ycombinator response, led to what I think is my most popular tweet of all time (which, compared to many others, isn't that big of a deal):

But I was also asked what the heck the code is doing, so let me walk you through it.

Object-Relational Mappers (ORMs) are awesome if you think about your database a collection of objects. They're awful if you think about your database as a database. My primary client right now is ZipRecruiter and my work involves rewriting one of their internal systems to be more flexible. However, it involves tons of reporting. For example, I have this SQL:

  SELECT var.name, ce.event_type, count(*) as total
    FROM tracking_conversion_event ce          JOIN tracking_visitor visitor      ON visitor.tracking_visitor_id      = ce.tracking_visitor_id
    JOIN tracking_version_variant curr ON curr.tracking_version_variant_id = visitor.tracking_version_variant_id
    JOIN tracking_version ver          ON ver.tracking_version_id          = curr.tracking_version_id
    JOIN tracking_variant var          ON var.tracking_variant_id          = curr.tracking_variant_id
   WHERE ver.tracking_id = ?
     AND ver.version     = ?
GROUP BY 1, 2

What you can't see in that SQL is that there are many subtleties in it:

• That count(*) as total sometimes needs to be replaced by sum(ce.num_events) as total
• Sometimes I need different sets of fields returned (and that impacts the GROUP BY, too)
• One of those JOIN statements sometimes needs to be a LEFT JOIN.

As you can imagine, that's frustrating. These are the sorts of things that don't map too well to an ORM. As a result, I've hard-coded some SQL and used $dbh->selectall_arrayref, and repeatedly struggled with the fact that sometimes the columns can be in different positions of that array ref.

Or I could return hashrefs and wonder if $result->{total} is really 0, or did I write $result->{totla} again?

Or in another case I found, we had a resultset return many rows of data with about 20 columns per row, but we only needed 4 or 5 columns. Restricting our data to only what we need not only makes the queries faster, it can reduce I/O when you're fetching results from another server. Class::DBI tried to allow this, but that only works at a class level, not query-by-query.

In reality, everyone here is used to working with DBIC resultsets. Returning complex data structures and having people try to remember which index maps to which column, or if the hash keys have changed is frustrating. Wouldn't it be nice if we could just take a chunk of arbitrary SQL and get a resultset from it? Well, now you can.

Update: I really can't say as much as I would like (there's stuff I can't share), but my publisher had a face-to-face with an Amazon rep and internal action was taken. Amazon's investigation is apparently over. The internal position seems to be "we're making money, there are words on pages, so there's no problem here." Amazon's investigation was short and sweet. Some bogus reviewers were removed, but "Felicity" -- one of the worst offenders -- is still there, despite the obvious fact that these are fake reviews. Many other obviously fake reviews remain. In fact, a new fake book with fake reviewers showed up. I genuinely do not know if this response is because of a careless employee or if Amazon discourages employees from shutting down profit streams.

Update 2: At least two of the fraudulent books are now 404s. It appears that Amazon may be taking action after all :)

Update 3: The books are still there. They may not be showing up on the Perl page any more, but Amazon is leaving this near-fraudulent material available for sale. I've dug into some of the books as well as I can and it looks like someone is copying material from Wikipedia, a few online tutorials, and putting together cheap, poorly written eBooks with only a few pages of material.

Most of you know that I wrote Beginning Perl and it's gotten great reviews. In fact, the only significant negative point which comes up is the number of typos. I think that's fair. I wrote the book over a few months, just after my daughter was born, and it is, I think, the single longest Perl book by a single author. It was a Herculean task. Nonetheless, people say it's a great book and it's routinely recommended to new developers. In fact, I've had companies tell me that they buy copies for their devs learning Perl. That's quite an accomplishment.

My book has bounced around on the rankings, sometimes being #1 in Perl (not as often as I would like), but usually down a bit further past well-deserving books by great authors such as Randal Schwartz, Damian Conway, chromatic, and so on. Nonetheless, sales have been steady enough that I'm pleased -- and relieved. As of this writing, my book is the twelfth most popular Perl book, but I realized it's not in the top ten because of what appears to be ... well ... decide for yourself.

My FOSDEM talk, Perl 6 for Mere Mortals, is now online:

You can see the rest of the Perl dev room videos here. Sadly, there were some technical problems, so a couple of videos have audio issues.

All of FOSDEM videos will eventually be here, but not all videos are ready yet.