As always, any and all advice and criticism is welcome.
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.
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>
...
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>
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;
}
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),
} ,
);
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 %]
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;
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;
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;
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;
