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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org [mailto:rubyonrails-talk@googlegroups.com] På vegne af Fresh Mix Sendt: 21. december 2011 12:04 Til: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org [mailto:rubyonrails-talk@googlegroups.com] På vegne af Brynjolfur Thorvardsson Sendt: 21. december 2011 13:02 Til: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org [mailto:rubyonrails-talk@googlegroups.com] På vegne af Fresh Mix Sendt: 21. december 2011 12:04 Til: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Brynjolfur Thorvardsson wrote in post #1037658:> (SELECT uid2 AS uid, status FROM friendships WHERE uid1 = 1) > UNION > (SELECT uid1,status FROM friendships WHERE uid2 = 1)It works fine, but it is slow :( Only one entry per friendship (1 500 000 rows in database) SELECT with UNION: 1.2108 sek Dublicate entryes ( total 3 000 000 rows in database) SELECT without UNION: 0.0007 sek -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
> (SELECT uid2 AS uid, status FROM friendships WHERE uid1 = 1) UNION > (SELECT uid1,status FROM friendships WHERE uid2 = 1)How to write same in Rails 3 Active Record Query format? -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
It would probably run a lot faster if you had a view and separate indexes on the two uid columns. I haven''t got access to my MySQL at the moment, so i can''t check the syntax for creating a view but you should be able to find that yourself. Something like: CREATE VIEW friendshipview AS (<the union select statement>) Creating separate indexes on the columns used in the select statements should speed things up quite a bit, an index should speed up by a factor of 10^2 or 3. If you have a very active database you might also consider rebuilding the index regularly, with OPTIMIZE TABLE <table name>. -----Oprindelig meddelelse----- Fra: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org [mailto:rubyonrails-talk@googlegroups.com] På vegne af Fresh Mix Sendt: 22. december 2011 21:33 Til: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org Emne: [Rails] Re: SV: Simple friendship table Brynjolfur Thorvardsson wrote in post #1037658:> (SELECT uid2 AS uid, status FROM friendships WHERE uid1 = 1) UNION > (SELECT uid1,status FROM friendships WHERE uid2 = 1)It works fine, but it is slow :( Only one entry per friendship (1 500 000 rows in database) SELECT with UNION: 1.2108 sek Dublicate entryes ( total 3 000 000 rows in database) SELECT without UNION: 0.0007 sek -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
A simple way would be to just call <model>.find_by_sql(<sql query>)
On the other hand, if you create a view in MySQL to handle the query, say the
view is called "friends", then you should be able to create a model
like this:
app/models/friend.rb
class Friend < ActiveRecord::Base
end
Obviously, depending on the view and tables behind it, you might want to make
your new model read only. That can apparently be done like this (I
haven''t actually tested this!)
class Post < ActiveRecord::Base
def readonly?
return true
end
end
There are probably lots of other ways of wrapping the result as an object, but
basically I don''t think you can avoid passing the SQL UNION statemenet
as is. There might be a way of calling two separate SQL statements, store the
results in separate variables and then adding the two:
@var1 = @friends.where(<select 1>)
@var2 = @friends.where(<select 2>)
@friendsfound = @var1 + @var2
Obviously, the returned column names would have to match, and I''m not
at all sure this would do what it looks like doing!
-----Oprindelig meddelelse-----
Fra: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
[mailto:rubyonrails-talk@googlegroups.com] På vegne af Fresh Mix
Sendt: 22. december 2011 23:53
Til: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
Emne: [Rails] Re: SV: Simple friendship table
> (SELECT uid2 AS uid, status FROM friendships WHERE uid1 = 1) UNION
> (SELECT uid1,status FROM friendships WHERE uid2 = 1)
How to write same in Rails 3 Active Record Query format?
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
On 21 December 2011 11:04, Fresh Mix <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> 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..First setup the model relationships so that you are not messing about with id values all the time, something like this railscast [1]. Then if you want all the friends of a user it is simply. user.friends + user.inverse_friends Colin [1] http://railscasts.com/episodes/163-self-referential-association -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Brynjolfur Thorvardsson wrote in post #1037995:> @var1 = @friends.where(<select 1>) > @var2 = @friends.where(<select 2>) > > @friendsfound = @var1 + @var2tmp1 = Friendships.includes(:users).select("uid1 as user_id", status).where("uid2 = (?)", @user.id) tmp2 = Friendships.includes(:users).select("uid2 as user_id", status).where("uid1 = (?)", @user.id) @friends = tmp1 + tmp2 Tested, it works, but the only problem is ordering. How to order: .order("FIELD( friendships.status, ''lover'', ''best friend'', ''partner'', ''enemy'' ), users.name") -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On 24 December 2011 18:50, Fresh Mix <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> Brynjolfur Thorvardsson wrote in post #1037995: > >> @var1 = @friends.where(<select 1>) >> @var2 = @friends.where(<select 2>) >> >> @friendsfound = @var1 + @var2 > > > tmp1 = Friendships.includes(:users).select("uid1 as user_id", > status).where("uid2 = (?)", @user.id) > tmp2 = Friendships.includes(:users).select("uid2 as user_id", > status).where("uid1 = (?)", @user.id) > @friends = tmp1 + tmp2 > > Tested, it works, but the only problem is ordering. > > How to order: > > .order("FIELD( friendships.status, ''lover'', ''best friend'', ''partner'', > ''enemy'' ), users.name")Because you are adding the two collections together you will have to sort it afterwards. Have a look at Array#sort and sort_by. Colin -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.