[CDBI] order by a foreign key
m.romani at spinsoft.it
Tue Aug 16 16:34:23 BST 2005
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
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
(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)
More information about the ClassDBI