Ovid

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,
                  name,
                  slug,
                  available,
                  dialog.character_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
SQL

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')
                  ->find($npc_id);
        },
        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.