many-to-many ordering

[prev] [thread] [next] [Date index for 2004/06/18]

I've got a many to many relationship which works great, but I'd like to
be able to get results back in a sorted by a value in the target column.

The app I have has images, and subject terms which may be mapped to each
other many to many, and I'd like to be able to call:

    @subjects = $image->subjects();

And get the subject sorted by name. I tried using an order_by clause
with the result below. Any advice, tips, pointers would be welcome

//Ed

--

package Photodex::Image;
__PACKAGE__->table( 'image' );
__PACKAGE__->columns( Primary => qw( id ) );
__PACKAGE__->columns( Others => qw( description date height width ) );
__PACKAGE__->has_many( subjects => [ 'Photodex::ImageSubject' => 'subject_id' ],
    'image_id', { order_by => 'subject.name' } );

package Photodex::ImageSubject;
use base qw( Photodex::DBI );
__PACKAGE__->table( 'image_subject' );
__PACKAGE__->columns( Primary => qw( image_id subject_id) );
__PACKAGE__->has_a( image_id => 'Photodex::Image' );
__PACKAGE__->has_a( subject_id => 'Photodex::Subject' );

package Photodex::Subject;
use base qw( Photodex::DBI );
Photodex::Subject->table( 'subject' );
Photodex::Subject->columns( Primary => qw( id ) );
Photodex::Subject->columns( Others => qw( name type ) );
Photodex::Subject->has_a( type => 'Photodex::SubjectType' );
Photodex::Subject->has_many( images => [ 'Photodex::ImageSubject' =>
'image_id' ], 'subject_id' );

[ed@chloe Photodex]$ prove -I ../ Subject.t
Subject....ok 4/0Photodex::ImageSubject can't SELECT image_id,
subject_id
FROM   image_subject
WHERE  image_id = ? ORDER BY subject.name
: DBD::SQLite::st execute failed: no such column: subject.name [for
Statement "SELECT image_id, subject_id
FROM   image_subject
WHERE  image_id = ? ORDER BY subject.name
"] at /usr/local/lib/perl5/site_perl/5.8.2/DBIx/ContextualFetch.pm line
51.
 at
 /usr/local/lib/perl5/site_perl/5.8.2/Class/DBI/Relationship/HasMany.pm
 line 118