Just a small addition to my previous email. If you also want to see, from the result, whether user 1 initiated the friendship (i.e. where uid1=1), you could use the following SQL statement:
(SELECT uid2 AS uid, status, 1 AS initiated FROM friendships WHERE uid1 = 1) UNION (SELECT uid1,status,0 FROM friendships WHERE uid2 = 1) This would give you the following result (using same data as below): Uid, status, initiated 2,"best friend",1 3,"partner",1 4,"enemy",0 7,"buddy",0 Where initiated=1 are the ones with uid1=1 and initiated=0 are the ones with uid2=1 -----Oprindelig meddelelse----- Fra: rubyonrails-talk@googlegroups.com [mailto:rubyonrails-talk@googlegroups.com] På vegne af Brynjolfur Thorvardsson Sendt: 21. december 2011 13:02 Til: rubyonrails-talk@googlegroups.com Emne: SV: [Rails] Simple friendship table Hi, you only need one entry per friendship (where, for extra functionality, the first id could always be the one who initiated the friendship). Friendships table could be something like have (uid1, uid2, status). Probably put first two as primary key. To find ids of all friends of user 1 you would do : (SELECT uid2 AS uid, status FROM friendships WHERE uid1 = 1) UNION (SELECT uid1,status FROM friendships WHERE uid2 = 1) This would return a set of rows with columns (uid, status) for all friends of user 1. If you have: Uid1,uid2,status 1,2,"best friend" 1,3,"partner" 3,2,"lover" 3,7,"barely met" 4,1,"enemy" 7,1,"buddy" The above select would return: Uid, status 2,"best friend" 3,"partner" 4,"enemy" 7,"buddy" Regards Binni -----Oprindelig meddelelse----- Fra: rubyonrails-talk@googlegroups.com [mailto:rubyonrails-talk@googlegroups.com] På vegne af Fresh Mix Sendt: 21. december 2011 12:04 Til: rubyonrails-talk@googlegroups.com Emne: [Rails] Simple friendship table Tables: Users (id, name, sex) Friendships (user_id, friend_id, status) If user(1) and user(2) are friends shoud I add two rows into friendships table? 1, 2, "best friends" 2, 1, "best friends" Or is it enough to have one of these rows? Select * from friendships where user_id = 2 or friend_id = 2 ???? But if I have only one friendship row and I need to list all friens of user(2) How can I do it? I don't know which one "user_id" or "friend_id" is needed "data". Of course, I can check: IF search_id != friend_id print (friend_id).user.name ELSE print (user_id).user.name But it is slow and stupid.. -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-talk+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-talk+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-talk+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
