hi,
I have to settle a database query problem, and I am a bit confused.
I have 4 tables:
-----------------
Members
Groups
Roles
Memberships: member_id, group_id, role_id
Messages: concerned_group_id, content
If a member is logged in the application, I would like to display the
messages, regarding the groups (if any) of which the member is admin.
(admin is a role)
I don''t know what is the best way to do that, but:
First I can find all Memberships, where the member is admin:
admin_role_id = Role.find_by_name("admin").id
Membership.find(:all, :conditions => [''member_id = ? and role_id =
?'',
member.id, admin_role_id])
Each from the found Memberships has a group_id
Then how can I find all the Messages, that concern the groups with these
group_id-s?
Thank you!
--
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
That appears to be 5 tables, but I presume that is just a typo. What are the has and belongs to relationships? 2009/3/5 Pesho Petrov <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>> > hi, > I have to settle a database query problem, and I am a bit confused. > I have 4 tables: > ----------------- > Members > Groups > Roles > Memberships: member_id, group_id, role_id > Messages: concerned_group_id, content > > If a member is logged in the application, I would like to display the > messages, regarding the groups (if any) of which the member is admin. > (admin is a role) > > I don''t know what is the best way to do that, but: > First I can find all Memberships, where the member is admin: > admin_role_id = Role.find_by_name("admin").id > Membership.find(:all, :conditions => [''member_id = ? and role_id = ?'', > member.id, admin_role_id]) > > Each from the found Memberships has a group_id > Then how can I find all the Messages, that concern the groups with these > group_id-s? > > Thank you! > -- > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Colin Law wrote:> That appears to be 5 tables, but I presume that is just a typo.yes, sorry for the typo.> What are the has and belongs to relationships?Member: -------- has_many :memberships, :dependent => :destroy has_many :roles, :dependent => :destroy has_many :messages, :dependent => :destroy Group: ------- has_many :mberships,:dependent => :destroy has_many :messages, :dependent => :destroy Role: ------ has_many :memberships, :dependent => :destroy has_many :messages, :dependent => :destroy Membership: ------------ belongs_to :member belongs_to :group belongs_to :role Message: ----------- belongs_to :member belongs_to :role belongs_to :group :) -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
2009/3/5 Pesho Petrov <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>> > Colin Law wrote: > > That appears to be 5 tables, but I presume that is just a typo. > > yes, sorry for the typo. > > > What are the has and belongs to relationships? > > Member: > -------- > has_many :memberships, :dependent => :destroy > has_many :roles, :dependent => :destroy > has_many :messages, :dependent => :destroy > > Group: > ------- > has_many :mberships,:dependent => :destroy > has_many :messages, :dependent => :destroy > > Role: > ------ > has_many :memberships, :dependent => :destroy > has_many :messages, :dependent => :destroy > > Membership: > ------------ > belongs_to :member > belongs_to :group > belongs_to :role > > Message: > ----------- > belongs_to :member > belongs_to :role > belongs_to :group >Are you sure? That seems incredibly complex. For example a member has many roles, but it also has many memberships, each one of which belongs to a role, so that is another set of roles a member may have. Does a message belong to a member, a group and a role or just one of them at a time?> > > > :) > > -- > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Colin Law wrote:> Are you sure? That seems incredibly complex. For example a member has > many > roles, but it also has many memberships, each one of which belongs to a > role, so that is another set of roles a member may have. > > Does a message belong to a member, a group and a role or just one of > them at > a time?ok Colin, you''re absolutely right! I''m afraid I''ve totally messed up my dependencies. This is my first time to handle database relationships through Rails models. So, I believe this is what I want to have: Member: has_many :memberships, :dependent => :destroy has_many :messages, :dependent => :destroy Group: has_many :memberships, :dependent => :destroy has_many :messages, :dependent => :destroy Role: has_many :memberships, :dependent => :destroy Membership: belongs_to :member belongs_to :group belongs_to :role Message: belongs_to :member belongs_to :group A message is supposed to be written by a member, who is a part of group. So I believe a message should belong to a member and a group. But different members from the same group can write different messages. And also, the same member, who is a part of different groups can write different messages on behalf of the different groups he/she is a part of. I hope the relationships above reflect well my expectations that I just described. Thanks in advance! -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Am I right in thinking that the member/group combination for a message is always the same member/group that appears in a membership record? If so then would it be better to have Membership: has_many :messages belongs_to :member belongs_to :group belongs_to :role and Message: belongs_to :membership Then you don''t need the has_many :messages for Member or Group and I think the whole thing is simpler. 2009/3/5 Pesho Petrov <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>> > Colin Law wrote: > > > Are you sure? That seems incredibly complex. For example a member has > > many > > roles, but it also has many memberships, each one of which belongs to a > > role, so that is another set of roles a member may have. > > > > Does a message belong to a member, a group and a role or just one of > > them at > > a time? > > ok Colin, you''re absolutely right! I''m afraid I''ve totally messed up my > dependencies. This is my first time to handle database relationships > through Rails models. > > So, I believe this is what I want to have: > > Member: > has_many :memberships, :dependent => :destroy > has_many :messages, :dependent => :destroy > > Group: > has_many :memberships, :dependent => :destroy > has_many :messages, :dependent => :destroy > > Role: > has_many :memberships, :dependent => :destroy > > Membership: > belongs_to :member > belongs_to :group > belongs_to :role > > Message: > belongs_to :member > belongs_to :group > > A message is supposed to be written by a member, who is a part of group. > So I believe a message should belong to a member and a group. > But different members from the same group can write different messages. > And also, the same member, who is a part of different groups can write > different messages on behalf of the different groups he/she is a part > of. > > I hope the relationships above reflect well my expectations that I just > described. > > Thanks in advance! > -- > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Colin Law wrote:> Am I right in thinking that the member/group combination for a message > is > always the same member/group that appears in a membership record? If so > then would it be better to have > > Membership: > has_many :messages > belongs_to :member > belongs_to :group > belongs_to :role > > and > > Message: > belongs_to :membership >you are right. Then should I remove the member_id and group_id fields from table Messages, and substitute them with a field membership_id? :) -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
2009/3/5 Pesho Petrov <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>> > Colin Law wrote: > > Am I right in thinking that the member/group combination for a message > > is > > always the same member/group that appears in a membership record? If so > > then would it be better to have > > > > Membership: > > has_many :messages > > belongs_to :member > > belongs_to :group > > belongs_to :role > > > > and > > > > Message: > > belongs_to :membership > > > > you are right. Then should I remove the member_id and group_id fields > from table Messages, and substitute them with a field membership_id? >Yes. The question is, of course, has any of this helped with your original question? I think it has as you can now find all messages where the membership.role_id is admin and membership.member_id is the member you want> > :) > -- > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Colin Law wrote:> Yes. The question is, of course, has any of this helped with your > original > question? I think it has as you can now find all messages where the > membership.role_id is admin and membership.member_id is the member you > wantyes, we are back to the first question :) you are right. This is the logic, but how do I express it in Rails syntax?? (I think) I cannot use Message.find, because the Message model doesn''t have info about roles... Best, Petar -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
2009/3/6 Pesho Petrov <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>> > Colin Law wrote: > > > Yes. The question is, of course, has any of this helped with your > > original > > question? I think it has as you can now find all messages where the > > membership.role_id is admin and membership.member_id is the member you > > want > > yes, we are back to the first question :) > you are right. This is the logic, but how do I express it in Rails > syntax?? > (I think) I cannot use Message.find, because the Message model doesn''t > have info about roles... >Yes it does, the relationships give it that information. If you have a message then you can say a_message.membership.role. You can also do something like Message.find(:all, :include => :membership, :conditions => [''membership.role_id =?'', a_role_id]) I am not guaranteeing the exact syntax, I may have made a silly mistake as I have not done much yet myself. Possibly a bit of a case of the blind leading the blind. Play about a bit and look in development.log to see the actual sql if it is not working for you.> > Best, > Petar > -- > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
lol, that''s great! THANKS A LOT, Colin ! ! ! Have a great weekend :) Petar -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---