[Dbix-class] How to setup a complicated (bilateral) relationship?

[Matt S Trout]

On Fri, Jun 22, 2007 at 04:59:29PM -0400, Steve Francia wrote:
> Matt S Trout wrote:
> > On Fri, Jun 22, 2007 at 01:09:15PM -0400, Steve Francia wrote:
> >   
> >> I have a rather complicated relationship that I am struggling with
> >> setting up, hopefully I can explain it so it makes sense. I will also
> >> include what we have thus far.
> >>
> >> I have two tables user and friends, friends is a join table that joins
> >> users to users. In my system, friendship is bilateral, meaning to have a
> >> friend is to be one.
> >> I worked through the reciprocal relationship alright, but the bilateral
> >> part has me stumped.
> >>     
> >
> > You can use an OR in the join cond, although it's not always as efficient
> > on MySQL as you might like -
> >
> > has_many(friend_link => ...::Friend => [
> >            { 'foreign.f1_id' => 'self.id' },
> >            { 'foreign.f2_id' => 'self.id' },
> >          ]);
> >
> > which will do
> >
> > user me JOIN friend friend_link
> > ON (friend_link.f1_id = me.id OR friend_link.f2_id = me.id)
> >
> >   
> Thanks Matt,
> This is nearly there, certainly a lot closer than I had on my own.. But
> it isn't quite right.
> The way this is currently it joins half the relationships back to the
> original user, so $user->friend is the $user itself.
> I believe this is because the relationship is only setup on one of the
> keys, even though we use both for the join.
> Perhaps this cannot be accomplished with one relationship, and two are
> needed? friends, and myfriends and we will join them outside of the model?
> -Steve
 
> BTW there are various reasons why we are not using two rows. 1. being,
> it is messier from a database point of view. 2. We always want bilateral
> relationships, and never unilateral ones (I am your friend, but you are
> not mine). 3. It is more maintainable. 4. It is more scalable when
> dealing with large number of rows..

I agree on 1-3, but you're using mysql, aren't you?

Memory sayeth the mysql query planner tends to really crap itself on OR-based
joins; while the plural of anecdote is not data I do hope you've benchmarked
(4) and are certain of its correctness - and if you have, would you care to
share the results?

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director    Want a managed development or deployment platform?
 Shadowcat Systems Ltd.  Contact mst (at) shadowcatsystems.co.uk for a quote
http://chainsawblues.vox.com/             http://www.shadowcatsystems.co.uk/