Custom DBIx::Class ResultSets

On my personal blog I wrote about Veure, an MMORPG that I'm writing in Perl. I followed that up with a post about the roadmap to an invite-only alpha. It's a lot of work, but my company has now decided to commit to it and figure out how to finance the work. This browser game is huge in intended scope, but fortunately, Perl has given me the power to get much of it done quickly. In fact, according to my private Veure github repo, I now have 17% of the ALPHA tasks done. That's up from 0% when I posted the roadmap a little over a week ago. In short, progress is fast.

Currently I'm working on character combat and that's where custom DBIx::Class resultsets have made my life easier.

There are many constraints on combat, including:

  • You can't attack yourself
  • No attack can last longer than five minutes
  • You can't attack more than one person at a time

I'm a firm believer that if I have invariants that I can represent at the database level, I should. Sure, I can check for them at the code level, but if I forget, I don't want to take the chance that corrupted data gets into the database. Thus, my table definition for combat looks similar to this (this is Postgresql):

CREATE TABLE combat (                       
    combat_id    SERIAL NOT NULL,
    attacker_id  INTEGER NOT NULL,
    defender_id  INTEGER NOT NULL CHECK (defender_id <> attacker_id),
    combat_start TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp,
    FOREIGN KEY (attacker_id) REFERENCES character(character_id),
    FOREIGN KEY (defender_id) REFERENCES character(character_id),
    UNIQUE (attacker_id) -- you can only *start* one fight at a time

Before I discuss the custom resultsets, I want to cover a few things about the above table. First, note that all IDs, even PKs, are explicitly spelled out as combat_id, character_id, and so on. That helps to avoid cases where there's ambiguity in the SQL like = this.character_id. When I changed to explicitly spelling out the IDs, I discovered a few places in my code where $object->id wasn't always clear, but changing that to $object->ship_id made it perfectly clear (that's an example; I don't use variable names like $object). I used to use id as the name of surrogate keys, but I was wrong, so I stopped that.

Further, the CHECK constraint on defender_id means that no character can attack itself. Again, even if I forget to code that in somewhere, the database will throw an error. Also, the unique attacker id means that I can only start one fight with one character at a time (though several characters can attack a single character at once, if desired).

This led to a very interesting situation where this fails to correctly start a combat:

    attacker_id => $attacker_id,
    defender_id => $defender_id,

Superficially that looks right but it's a serious bug. Remember where I said that attacks can't last longer than 5 minutes? That satisfies the case where someone starts a fight and gets a network failure, walks away, or whatever. However, if that does a find instead of a create, it doesn't reset the combat start time. Thus, we have the following custom resultset:

package Veure::Schema::ResultSet::Combat;

use strict;
use warnings;
use DateTime;
use parent 'DBIx::Class::ResultSet';

sub start_combat {
    my ( $self, $attacker, $defender ) = @_;
    my $combat = $self->find_or_create(
        {   attacker => $attacker,
            defender => $defender,
    $combat->combat_start( DateTime->now );
    return $combat;


Now, we can call $schema->resultset('Combat')->start_combat( $attacker, $defender ) and it will ensure that the time was properly updated.

That's not the only way this is useful. In our Character class, we have the following defined:

  { "foreign.defender_id" => "self.character_id" },
  { cascade_copy => 0, cascade_delete => 0 },

A character can only attack one other character at a time, but several characters can attack a character at once, hence the above definition automatically created by DBIx::Class::Schema::Loader. But that doesn't really tell us if the attacks started in the last five minutes ... an important factor. Instead, we have the following in our combat resultset:

sub active_combats {
    my ( $self, $character ) = @_;          

    # return combats which have started less than 5 minutes ago
    return $self->search(
        { "( now() - combat_start ) < interval '5 minutes'" => 1 } );

(Note: I'd prefer to write that as ( now () - combat_start ) => { '<', "interval '5 minutes'" }, but that puts the interval string into a bind parameter and PG throws an exception over an invalid type)

The above method, when called on the resultset, returns all combats that started within the last five minutes. Because resultsets are composable, in my character class, I have this:

sub in_combat_as_defender {
    my $self = shift;
    return $self->combat_defenders->active_combats->count;

The combat_defenders resultset will only return combats with the defender matching the current character_id. However, when active_combats is called on that resultset, it further restricts it. In DBIx::Class, no SQL is executed until you actually need data (the count in the above method). As a result, the resulset restrictions all get combined until only a single SQL query is generated. In other words, the above is actually a fairly efficient and readable way to fetch this data.

The next time you find yourself putting complicated logic into a schema result class, ask yourself if you can push it to the database or, barring that, push it into a resultset class to make it harder for your code to accidentally subvert your intentions.


Brilliant use of custom result sets. I use them too, but not nearly as much as I should.

Oh right, you’re writing a game. I forgot.

You probably want to be aware of the entity, component, system pattern. Here is an explanation that made sense to me (via Wayback Machine as the blog is currently offline).

Squinting at your game I can’t decide whether it’s applicable and a win for your case, so that’s your call. Either way it’s a very cool pattern and fun to know of.

I might be mistaken but I think DBIx allows you to write your 5-minutes query like this:

return $self->search({
    combat_start => { '>' => \"NOW() - INTERVAL '5 minutes'" }

This puts the column you're searching on (combat_start) alone on the left of the comparison in the WHERE clause:

WHERE combat_start > NOW() - INTERVAL '5 minutes'

... allowing Postgres to use an index when performing the search!

Leave a comment

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!