Improved DBIx::Class usage with arbitrary SQL

A few months ago I write about using arbitrary SQL to generate DBIx::Class resultsets. These DBIx::Class::Report resultsets are read-only, but I found that I needed to add additional methods to each result and now I can. This makes the software much more flexible.

I had a case for Veure where I needed to be able to create dialogs for NPCs, but I also needed to track whether a dialog had been "completed" for a given character (so as to not present the same dialog again and have people walk every dialog branch). This means that the dialog table needs to fetch dialogs for a given NPC, but ensure that the dialog_completed table doesn't have an entry for that dialog, for a given player. That's a bit tricky with DBIx::Class, but with DBIx::Class::Report, it's trivial.

However, DBIx::Class::Report was returning raw data and I needed to "inflate" some data to make it useful. Thus, you can now attach methods to individual result objects. They look like this:

my $sql = <<'SQL';
           SELECT dialog.dialog_id,
                  conversation as _conversation
             FROM dialog
  LEFT OUTER JOIN completed_dialogs ON completed_dialogs.dialog_id = dialog.dialog_id
            WHERE dialog.character_id = ?
              AND dialog.dialog_id NOT IN (
                     SELECT dialog_id
                       FROM completed_dialogs
                      WHERE character_id = ?
         ORDER BY name

my $npc_id       = $npc->character_id;
my $report       = DBIx::Class::Report->new(
    schema => $self->result_source->schema,
    sql    => $sql,
    columns =>
      [qw/ dialog_id name slug available character_id _conversation /],
    methods => {
        character => sub {
            my $self = shift;
            return $self->result_source->schema->resultset('Character')
        conversation => sub {
            my $self = shift;
            return JSON->new->decode( $self->_conversation );
return $report->fetch( $npc_id, $character->character_id );

And in the actual code, you can use the results just like a full-blown read-only dbic class, including getting a data structure instead of raw JSON, and calling $result->character to get the NPC object instead of just the ID. The read-only part might frustrate some, but for how our mechanics work internally, having a "read-only" resultset works out nicely here.


Sounds interesting. Since reports tend to become long, does it support paged results (like normal result sets ) ? Rolf

So I tried it and it works. Very good ! Rolf

I have to thank you for DBIx::Class::Report. It helped quite a bit when I was trying to do some fairly nasty things with DBIC.

It's the base I use for a fairly complex report where I need to use a derived table, but I want that derived table to have DBIC relations. First I create the report, then I programmatically add all the might_have/belongs_to/has_one relationships to the derived table resultset, and then I can use the ResultSet just as if it had been statically defined, and access row relations the same way.

I played with trying to fold the capabilities into DBIx::Class::Report itself for a while so I could send a pull request, but I couldn't come up with an API that wasn't unwieldy and confusing enough to make it worthwhile. :/

About Ovid

user-pic Freelance Perl/Testing/Agile consultant and trainer. See for our services. If you have a problem with Perl, we will solve it for you. And don't forget to buy my book!