[CDBI] order by a foreign key

15:34 on 16 Aug 2005

Hallo everybody,
     I'm working with Class-DBI in a catalyst project, and I found a 
problem that I think is not directly related to Catalyst, but to 
Class-DBI (or perhaps to Class::DBI::Plugin::RetrieveAll).

Let me first describe the 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.

(just to clarify I know this is the obvious behaviour and I know how to 
solve this in SQL, but I was looking for a general solution that would 
fit into Class-DBI)

I've coded a solution (tested) into one of my model classes, but I would 
like to use it in other classes/projects without any cut'n'paste coding ;-)
So I'm wandering wether to modify Class::DBI::Plugin::RetrieveAll or to 
override retrieve_all_sorted_by in my base CDBI model class...

Has anybody had/solved this issue before ?

(Sorry for getting too Catalyst specific in the last part)

Marcello

_______________________________________________
ClassDBI mailing list
ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi