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

[Moritz Onken]

Tobias Kremer schrieb:
> I'm trying to implement a user/friendship relationship with DBIx::Class. For
> this purpose I have two classes/tables:
>
> 1) User - stores userdata (id, username etc.)
>
> 2) UserFriend - stores user-2-user relationships
>    (id, user_id, friend_user_id, date_confirmed etc.)
>
> 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
>
> Is this possible with DBIx relationships or do I have to create a custom
> method which runs a search query returning a ResultSet (maybe with
> ResultSetManager)? It works with two independant many-to-many relationships
> (e.g. friendships_requested, friendships_accepted) but I'd like to have only
> one resultset containing both.
>
> Hope I'm not making things more complicated than they are :)
>
> Thanks!
>
> --Tobias
>
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive: http://www.mail-archive.com/dbix-class@lists.rawmode.org/
>   
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.

Greets Mo