[Dbix-class] Multiple belongs_to relationships referencing same class

[Jess Robinson]

On Thu, 26 Apr 2007, Tobias Kremer wrote:

>>> Yeah, but that's exactly what I want to avoid. If somebody does
>>> $user->confirmed_friendships, he/she is not supposed to know who
>>> initiated the friendship (requestor or receiver). All he/she wants
>>> is a list of User objects (except $user) which are friends of $user.
>>> The same goes for the other two methods. Currently I can't see a
>>> sane way to accomplish this with DBIx::Class :(
>
>> Follow the advice from the other thread (make a view that unions the
>> table with itself, if possible use insert/update triggers to re-order
>> the two generic user columns in a consistent manner (and if doing
>> that, then add a third field for who initiated it)), and then add that
>> view to your schema with a relationship to user so that you can query
>> it.
>
> I just checked this out with 100.000 user/friendship entries and the
> view is slow as hell :( Although this is in fact the cleanest solution
> I've seen up until now it's also the slowest.
>
> The main problem is: I have to make a somewhat complex union query on the
> friendship-table which should then automagically resolve the resulting
> user_ids into User objects. Maybe there's some DBIx::Class magic that
> allows me to run a custom query on another Class (UserFriendship, with
> $self->id() as a where condition) and returning objects of the current
> class (User).
>


I've not quite understood most of this thread, but it sounds like what you 
want is:

Table with user1_id, user2_id,

query that returns all user2_id matching a given user_id AND returns all 
user1_id that match when user2_is the given user_id.. correct?

I have this with DBIC, no unions involved ;)

My table is Relationships, with LItemID and RItemID, the rel is:

on Item: (an OR condition)
__PACKAGE__->has_many('rel_link', 'DB::Anything::Relationships',
 	                      [ { 'foreign.LItemID' => 'self.ID' },
 	                        { 'foreign.RItemID' => 'self.ID'} ]);

on Relationships: (an OR condition)
__PACKAGE__->has_many('attached_items', 'DB::Anything::Items',
 	                      [ { 'foreign.ID' => 'self.LItemID' },
 	                        { 'foreign.ID' => 'self.RItemID' } ],
 	                      {cascade_delete => 0});

in Item.pm:

sub connected_items
{
     my ($self) = @_;
     ## All items that arent this one:
     my $others = $self->rel_link->search_related('attached_items',
                 {
                  'attached_items.ID' => { '!=', $self->id }
                 },
                 { 'order_by' => 'attached_items.Name' }
     );

     return $others;
}


Jess