[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?

Unless you do a double-edged belongs_to on the join table, an extra restriction,
probably a distinct on top of ...

It -can- be accomplished with one. In theory. But I'm going to vote for doing
it with a pair of rels for clarity and to make it easier to tune the queries.

-- 
      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/