| Subject: | When ordering on a foreign key, sort by the foreign class' 'stringify' column |
To explain where this code comes from, let me describe a simple scenario:
I have two tables, 'cd' and 'artist', whose model classes are MyApp::Cd and MyApp::Artist.
MyApp::Artist is stringified as 'name'
The relationship is:
MyApp:Cd->has_a( artist => MyApp::Artist );
MyApp::Cd uses Class::DBI::Plugin::RetrieveAll, so I can say:
my @cds = MyApp::Cd->retrieve_all_sorted_by( $order ); # e.g. title ASC
And now to the point:
if $order == 'artist' the @cds list gets ordered by the artist *id*, not by the artist name.
What I would like to obtain is a list ordered by the foreign class's 'stringify' column.
This is desirable in applications where records are rendered in tabular form with "stringified" foreign keys and the user expects to be able to sort the rows by any of the displayed columns (e.g. CRUD webapps).
The code is rather verbose, and has strong limitations:
- suported ORDER BY clauses are of type <column> [ASC|DESC] (i.e. only 1 column + optional direction)
- both tables must have single-column primary keys
- foreign key Stringify column group must contain only 1 column
I felt it could be useful anyway. It was for me.
Others on the Class::DBI mailing list have suggested different solutions to my initial problem.
Please read the (very short) thread here:
http://lists.digitalcraftsmen.net/pipermail/classdbi/2005-August/thread.html#65
--- lib/Class/DBI/Plugin/RetrieveAll.pm 2004-08-02 13:28:47.000000000 +0200
+++ /usr/lib/perl5/site_perl/5.8.5/Class/DBI/Plugin/RetrieveAll.pm 2005-08-17 09:05:41.767253393 +0200
@@ -66,9 +66,40 @@
FROM __TABLE__
ORDER BY %s
+ $caller->set_sql(retrieve_all_sorted_foreign => <<'');
+ SELECT t.%s
+ FROM %s t
+ JOIN %s f ON t.%s=f.%s
+ ORDER BY %s
+
*{"$caller\::retrieve_all_sorted_by"} = sub {
my ($class, $order_by) = @_;
- return $class->sth_to_objects($class->sql_retrieve_all_sorted($order_by));
+ if (scalar(grep(/,/o, split(//,$order_by)))) {
+ warn "($order_by) Only single column order-bys are currently supported.";
+ return $class->sth_to_objects($class->sql_retrieve_all_sorted($order_by));
+ }
+ my ($field, $dir) = split /\s+/, $order_by;
+ my $meta = $class->meta_info('has_a');
+ if (exists($meta->{$field})) {
+ my $foreign_class = $meta->{$field}->foreign_class;
+ if (! $foreign_class->isa('Class::DBI')) { # e.g. CLass::Date
+ return $class->sth_to_objects($class->sql_retrieve_all_sorted($order_by));
+ }
+ my $foreign_table = $foreign_class->table;
+ my $foreign_pk = $foreign_class->primary_column;
+ my $foreign_stringify = ($foreign_class->columns('Stringify'))[0];
+ my $sth = $class->sql_retrieve_all_sorted_foreign(
+ $class->primary_column,
+ $class->table,
+ $foreign_table,
+ $field,
+ $foreign_pk,
+ $foreign_stringify . " " . $dir
+ );
+ return $class->sth_to_objects($sth);
+ } else {
+ return $class->sth_to_objects($class->sql_retrieve_all_sorted($order_by));
+ }
};
$caller->mk_classdata('__plugin_retall_sortfield');