Akoya has asked for the wisdom of the Perl Monks concerning the following question:

Update: per moklevat's request, I have copied the full text from Akoya's scratchpad to here for posterity.

As always, any and all advice and criticism is welcome.

Thanks, Akoya

BACKGROUND

I have developed a Perl web application using CGI::Application, Template Toolkit, JavaScript, DBIx::Class, and MySQL. CGI::Application, Template Toolkit, and DBIx::Class, are all new experiences for me. I have a fairly good grasp of the first two, but I have experienced a lot of trial and error with DBIx::Class. Overall, I am very pleased with the power and flexibility this combination offers.

PROBLEM STATEMENT

Creating an HTML table with sortable columns, where the table source is a paged DBIx::Class resultset containing multi-level relational data.

DETAILS

On the application's main page, there is a form, sortForm, with a nested table. The table has links in the column headers. For example: 

    ...
    <th width="15%"><a href="javascript:sort('location')">Location</a>
+</th>
    <th width="10%"><a href="javascript:sort('astrotype.name')">Type</
+a></th>
    <th width="10%"><a href="javascript:sort('terrain.name')">Terrain<
+/a></th>
    ...

[download]

The link passes a sort key to the javascript sort function, which then stores it in a hidden field, to be returned to the application:

    <script language="JavaScript" type="text/javascript">
    <!--
        function sort(col) {
            document.sortForm.sortCol.value=col;
            document.sortForm.submit();
        }
    //-->
    </script>

[download]

I chose to sort the columns this way, because the table only contains one page of a multi-page resultset, but I want the entire resultset sorted. Once the application receives the posted data, it passes the sort key to a function, which returns an array reference holding the column names to sort by:

    sub _sort_column {
        my $key = shift;
        my $array_ref = [];

        if( $key eq 'location' ) {
            # location is a pseudo-column,
            # containing these columns:
            $array_ref = [
                'astro.galaxy',
                'astro.region',
                'astro.system',
                'astro.astro',
            ];
        }
        else {
            $array_ref = [ $key ];
        }
        return $array_ref;
    }

[download]

This array ref is passed as the order_by value to the schema resultset search method. This search is returning a rows for a primary table (Bases), along with corresponding rows from a related table (Astros), and two tables related to astros (AstroTypes and AstroTerrains):

    my $sort_key = $query->param('sortCol')   || 
                   $session->param('sortCol') || 
                   'location';

    $bases_rs = $schema->resultset('Bases')->search(
        {
            profile_id => $profile->id,
        },
        {
            join          => {
                'astro'   => 'astrotype',
                'astro'   => 'terrain',
            },
            prefetch      => {
                'astro'   => 'astrotype',
                'astro'   => 'terrain',
            },
            order_by      => _sort_column($sort_key),
        } ,
    );

[download]

This column sorting technique works for ALL columns, except one: the name column from the AstroTypes table. I get the message that there is no such column 'astrotype.name'. I am dumbfounded as to why it is not working for that one, as the name column (terrain.name) from the AstroTerrains table sorts properly.

The related rows from the AstroTypes table, including the name column, display correctly on the page. The table rows are rendered as follows:

    [% FOREACH base IN bases %]
        [% IF base.astro %]
            <tr class="[% IF loop.index % 2 %]dark[% ELSE %]lite[% END
+ %]">
                <td width="15%">
                    [% base.astro.galaxy %]:[% base.astro.region %]:
                    [% base.astro.system %]:[% base.astro.astro %]</td
+>
                <td width="10%">[% base.astro.astrotype.name %]</td>
                <td width="10%">[% base.astro.terrain.name %]</td>

                ... lots of other columns removed for brevity

            </tr>
        [% END %]
    [% END %]

[download]

DATABASE DETAILS

The DBIx::Class schemas for the relevant tables are below. (namespace cleverly obfuscated ;) In a nutshell, there are 4 tables involved in this problem. Bases, Astros, AstroTypes, and AstroTerrains. A base belongs to one astro. An astro has one astro type, and one astro terrain.

I have verified that the schema definitions below match the table definitions in MySql.

The Bases Schema

package My::Namespace::DB::Bases;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("bases");
__PACKAGE__->add_columns(
  "id",          { data_type     => "BIGINT",    
                   default_value => undef, 
                   is_nullable   => 0, 
                   size          => 20 },
  "astro_id",    { data_type     => "BIGINT",    
                   default_value => undef, 
                   is_nullable   => 0, 
                   size          => 20 },
  "profile_id",  { data_type     => "BIGINT",    
                   default_value => undef, 
                   is_nullable   => 0, 
                   size          => 20 },

... lots of other columns removed for brevity ...

  "last_update", { data_type     => "TIMESTAMP", 
                   default_value => "CURRENT_TIMESTAMP", 
                   is_nullable   => 0, 
                   size          => 14, },
);
__PACKAGE__->set_primary_key("id");


# Created by DBIx::Class::Schema::Loader v0.04003 @ 2008-01-26 00:53:2
+3
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:RlHYEvMg3JniB7RL4miPvA

__PACKAGE__->belongs_to(
    profile => 'My::Namespace::DB::Profiles', 
    { 'foreign.id' => 'self.profile_id' },
);

__PACKAGE__->belongs_to(
    astro => 'My::Namespace::DB::Astros', 
    { 'foreign.id' => 'self.astro_id' }, 
    { cascade_delete => 0 },
);

1;

[download]

The Astros Schema

package My::Namespace::DB::Astros;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("astros");
__PACKAGE__->add_columns(
  "id",               { data_type     => "BIGINT", 
                        default_value => undef, 
                        is_nullable   => 0, 
                        size          => 20 },
  "galaxy",           { data_type     => "CHAR", 
                        default_value => "", 
                        is_nullable   => 0, 
                        size          => 3 },
  "region",           { data_type     => "TINYINT", 
                        default_value => "", 
                        is_nullable   => 0, 
                        size          => 3 },
  "system",           { data_type     => "TINYINT", 
                        default_value => "", 
                        is_nullable   => 0, 
                        size          => 3 },
  "astro",            { data_type     => "TINYINT", 
                        default_value => "", 
                        is_nullable   => 0, 
                        size          => 3 },
  "astro_type_id",    { data_type     => "TINYINT", 
                        default_value => "", 
                        is_nullable   => 0, 
                        size          => 3 },
  "astro_terrain_id", { data_type     => "TINYINT", 
                        default_value => "", 
                        is_nullable   => 0, 
                        size => 3 },

... lots of other columns removed for brevity ...

  "last_update",      { data_type     => "TIMESTAMP", 
                        default_value => "CURRENT_TIMESTAMP", 
                        is_nullable   => 0, 
                        size => 14,
  },
);
__PACKAGE__->set_primary_key("id");


# Created by DBIx::Class::Schema::Loader v0.04003 @ 2008-01-26 00:53:2
+3
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:L2UqQerufeVtYyq2WeydTQ

__PACKAGE__->has_many(
    bases => 'My::Namespace::DB::Bases', 
    { 'foreign.astro_id' => 'self.id' }, 
    { cascade_delete => 0 } 
);

__PACKAGE__->has_one(
    terrain => 'My::Namespace::DB::AstroTerrains', 
    { 'foreign.id' => 'self.astro_terrain_id' },
    { cascade_delete => 0 } 
);

__PACKAGE__->has_one(
    astrotype => 'My::Namespace::DB::AstroTypes',
    { 'foreign.id' => 'self.astro_type_id' },
    { cascade_delete => 0 } 
);

1;

[download]

The AstroTypes Schema

package My::Namespace::DB::AstroTypes;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("astro_types");
__PACKAGE__->add_columns(
  "id",          { data_type     => "TINYINT", 
                   default_value => undef, 
                   is_nullable   => 0, 
                   size          => 3 },
  "name",        { data_type     => "VARCHAR", 
                   default_value => "", 
                   is_nullable   => 0, 
                   size          => 20 },
  "last_update", { data_type     => "TIMESTAMP",
                   default_value => "CURRENT_TIMESTAMP",
                   is_nullable   => 0,
                   size => 14,
  },
);
__PACKAGE__->set_primary_key("id");


# Created by DBIx::Class::Schema::Loader v0.04003 @ 2008-01-26 00:53:2
+3
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:yoqPb2NOrr9JVquXfnYsog

__PACKAGE__->has_many(
    astros => 'My::Namespace::DB::Astros', 
    { 'foreign.astro_type_id' => 'self.id' }, 
    { cascade_delete => 0 },
);

1;

[download]

The AstroTerrains Schema

package My::Namespace::DB::AstroTerrains;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("astro_terrains");
__PACKAGE__->add_columns(
  "id",          { data_type     => "TINYINT", 
                   default_value => undef, 
                   is_nullable   => 0, 
                   size          => 3 },
  "name",        { data_type     => "VARCHAR", 
                   default_value => "", 
                   is_nullable   => 0, 
                   size          => 20 },

... lots of other columns removed for brevity ...

  "last_update", { data_type     => "TIMESTAMP",
                   default_value => "CURRENT_TIMESTAMP",
                   is_nullable => 0,
                   size => 14, },
);
__PACKAGE__->set_primary_key("id");


# Created by DBIx::Class::Schema::Loader v0.04003 @ 2008-01-26 00:53:2
+3
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:etM2yf14f59YAtaOo8ltSg

__PACKAGE__->has_many(
    astros => 'My::Namespace::DB::Astros', 
    { 'foreign.astro_terrain_id' => 'self.id' }, 
    { cascade_delete => 0 },
);

1;

[download]

Replies are listed 'Best First'.
Re: Perplexing DBIx::Class problem
by moklevat (Priest) on Feb 28, 2008 at 18:35 UTC
    The information in Akoya's scratchpad is not outrageously long, and for the sake of posterity it would be preferable for you to place it in your original node with <readmore> tags.
[reply]
[d/l]
      Thank you. I'll update my original node as requested.
[reply]
Re: Perplexing DBIx::Class problem
by peterdragon (Beadle) on Feb 29, 2008 at 13:01 UTC
    What does the SQL trace show when you run your code with environment variable DBIC_TRACE set? E.g. 
    $ DBIC_TRACE=1 perl myapp.pl

    [download]
    That should show the exact SQL being run, which will help track down what's wrong.
    --
    peterdragon ~ http://perl.dragonstaff.co.uk
[reply]
[d/l]
      Thank you. Being new to DBIx::Class, I wasn't aware of the DBIC_TRACE option. I will certainly give it a try when I get back to the project tonight.
[reply]

Back to Seekers of Perl Wisdom