Ovid

There's no question that DBIx::Class is the dominant ORM in Perl. That's because it's fast, it's flexible, and sane. Well, mostly sane, until you need some introspection (if anyone knows a better way to do this, I'm all ears!):

sub _get_json_columns ( $self, $schema_class ) {
    state $json_columns_for = {};
    unless ( exists $json_columns_for->{$schema_class} ) {
        my $columns = $self->schema->resultset($schema_class)
          ->result_source->columns_info;
        $json_columns_for->{$schema_class}
          = [ grep { 'json' eq $columns->{$_}{data_type} } keys %$columns ];
    }
    return $json_columns_for->{$schema_class};
}

But what's terribly frustrating to many devs is getting DBIx::Class to show the queries it's executing, along with the bind parameters (one without the other is often useless).

One thing you can do is use the DBIC_TRACE environment variable:

DBIC_TRACE=1 prove t/path/to/my/test.t

And then immediately regret the extra 873 lines of output to STDERR.

So you learn to direct the trace output to a file:

DBIC_TRACE=1=trace.out prove t/path/to/my/test.t

And then you find yourself staring at those 873 lines and wonder exactly which SELECT statement on the foobar table was wrong.

So you do this, because you're such a clever bugger:

sub available_for ( $self, $character ) {
    my $schema = $self->result_source;

    local $ENV{DBIC_TRACE} = '1=trace.out';

    my $result = $self->search(
        # complicated search arguments
    );
}

And then you wonder why that does nothing and a late night session of frantically googling and hitting stackoverflow finally leads you to the troubleshooting.pod for DBIx::Class.

You can't set DBIC_TRACE at runtime because it's only checked when the storage is built (but it apparently works if you trouble yourself to reconnect to your schema, something which may or may not be an option).

Instead, you can enable debugging on the fly with the storage object. Finally, you get down to this in the resultset class which is giving you trouble:

sub available_for ( $self, $character ) {
    my $storage = $self->result_source->storage;

    $storage->debug(1);    # start debugging
    $storage->debugfh(IO::File->new('trace.out', 'w'));    # write it to a file

    my $result = $self->search(
        # complicated search arguments
    );

    $storage->debug(0);    # end debugging
    return $result;
}

And you still get nothing. You're ready to scream and throw something at the wall.

The above code is correct, but there's one tiny hitch you may have forgotten about (or perhaps never realized).

In DBIx::Class, the query is never executed until you ask for the results of that query. You can think of that as lazy evaluation. This has two benefits. First, if you never use the results, you avoid a database hit. Second, it later lets you chain queries. Here's (a simplification) of code we have for the Tau Station MMORPG.

On every space station, there's work available. It might be a job, or it might be a mission. Here's how you could available work for a character

sub _get_available_work_for_character ( $self, $character ) {
    return $self->available->search( {
        'station_id' => $character->station->station_id,
        'available'  => 1,
    });
}

(Note that missions and jobs share the same table, but there's a boolean flag indicating which is which)

And if you want to find resultsets of missions or jobs:

sub get_jobs ( $self, $character ) {
    return $self->_get_available_work_for_character($character)
      ->search( { is_job => 1 } );   # here's the magic!
}

sub get_missions ( $self, $character ) {
    return $self->_get_available_work_for_character($character)
      ->search( { is_job => 0 } );   # here's the magic!
}

What that does is combine the new search parameters with the old and the resulting SQL is generated once, as needed.

Because of delayed execution and chained resultsets, it's very easy to refactor DBIx::Class applications, but it means our debugging example won't work until we fetch results. Rather than put our $storage->debug(0) far away where we use the results, just call count() on the resultset (or just fetch the results and call reset()) to see the SQL:

sub available_for ( $self, $character ) {
    my $storage = $self->result_source->storage;

    $storage->debug(1);    # start debugging
    $storage->debugfh(IO::File->new('trace.out', 'w'));    # write it to a file

    my $result = $self->search(
        # complicated search arguments
    );

    warn $result->count;      # force SQL to be executed
    $storage->debug(0);       # end debugging
    return $result;
}

I love hacking on DBIx::Class