[CDBI] Many-to-many relationships of same type?

Hi there,

I'm a newcomer to Class::DBI, and I'm having a problem - I need to
implement a many-to-many relationship where the objects involved are
both of the same type. The specific application is using data obtained
from LiveJournal, where users can have other users as 'friends'. I
can't seem to get this to work since it appears to use the first
object of this type. This is basically what I have in the relevant
files:


# ============== lib/LJData/User.pm


package LJData::User;
use base qw(LJData::DBI);

__PACKAGE__->table('users');
__PACKAGE__->columns(All => qw(userid username account_type
last_interests_fetch_date last_friends_fetch_date));

# NOTE: It's up to the code to ensure these are consistent!
__PACKAGE__->has_many(friends   => [ 'LJData::Friends' => 'friend' ]);
# __PACKAGE__->has_many(friendsof => [ 'LJData::Friends' => 'friendof' ]);

__PACKAGE__->has_many(interests => [ 'LJData::UserInterests' => 'interest' ]);

1;


# ============== lib/LJData/Interest.pm


package LJData::Interest;
use base qw(LJData::DBI);

__PACKAGE__->table('interests');
__PACKAGE__->columns(All => qw(intid intcount interest lastupdated));
__PACKAGE__->has_many(users => [ 'LJData::UserInterests' => 'user' ]);

1;


# ============== lib/LJData/Friends.pm


package LJData::Friends;
use base qw(LJData::DBI);

__PACKAGE__->table('friends');
__PACKAGE__->columns(Primary => qw(friendof friend));
__PACKAGE__->has_a(friendof => 'LJData::User');
__PACKAGE__->has_a(friend   => 'LJData::User');

1;


# ============== lib/LJData/UserInterests.pm


package LJData::UserInterests;
use base qw(LJData::DBI);

__PACKAGE__->table("user_interests");
__PACKAGE__->columns(Primary => qw(user interest));
__PACKAGE__->has_a(user     => 'LJData::User');
__PACKAGE__->has_a(interest => 'LJData::Interest');

1;


# ==============


The user<->interests mapping works fine, but the friend/friendof
mapping doesn't. I commented out the 'friendsof' has_many declaration
in User.pm to see if that was the problem but it isn't.

When running this short example program:


# ==============
#!/usr/bin/perl

use lib 'lib';

use LJData::User;
my $dbh = LJData::User->db_Main;
$dbh->trace(2);   # to enable SQL output

my $user = LJData::User->retrieve(129);
my $i = $user->interests;
my $f = $user->friends;
# ==============

The debug output shows that this SQL is being executed:

# ==============
  (for the retrieve:)
SELECT userid
FROM   users
WHERE  userid = '129'

  (for the interests:)
SELECT user, interest
FROM   user_interests
WHERE  user = '129'

  (for the friends:)
SELECT friendof, friend
FROM   friends
WHERE  friend = '129'
# ==============

The retireve and interests are going through properly but the friends
one should be using 'friendof' in the WHERE clause instead of 'friend'
(since we want to find all the friends of that user). Is this a bug in
Class::DBI when both ends are of the same type, or is it just
something that I'll have to override the 'friends' sub for?

Thanks,

 - Ciaran.