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]

In reply to Perplexing DBIx::Class problem by Akoya

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.