[Dbix-class] user/friendship many-to-many relationship

[Marc Mims]

* Moritz Onken <onken at houseofdesign.de> [070424 09:50]:
> Tobias Kremer schrieb:
> >Now I'd like to call $user->confirmed_friends to get a list of all 
> >confirmed
> >friendships (i.e. date_confirmed is set). The many-to-many relationship
> >functionality of DBIx does this without problems.
> >The catch is: A user is considered a friend if it is either stored as 
> >user_id
> >OR friend_user_id (depending on who initiated the request for friendship).
> >How would one do this with DBIx? The SQL where-condition is supposed to
> >look something like this:
> >
> >WHERE ( user_id = $user->id OR friend_user_id = $user->id )
> >AND date_confirmed IS NOT NULL
> >  
> I'd suggest to use a symmetric table schema with columns like userA and 
> userB.
> And if there is a new friendship insert twice: (112, 45) and (45, 112).
> Makes friends-of-friends queries easier and faster.

Expanding on that theme, I'd take a slightly different approach.

I would create a view:

create view friends_of (
   t1.user_id as primary, t1.friend_user_id as friend
   from friendship t1
   union
   t2.friend_user_id as primary, t2.user_id as friend
   from friendship t2
);

Then use friends_of to get the list of friends.

If you add a trigger to the friendship table that normalizes inserts,
for instance inserting the lowest id of a pair as user_id and the higher
id as friend_user_id, that would ensure relationships don't inadvertently
get added twice (e.g., (1,2), (2,1)).

	-Marc