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!
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 P. [email protected]
2009/3/5 Pesho P. [email protected]
has_many :memberships, :dependent => :destroy
has_many :memberships, :dependent => :destroy
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?
Colin L. 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
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 P. [email protected]
Colin L. 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!
2009/3/5 Pesho P. [email protected]
belongs_to :group
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
Colin L. 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?
2009/3/6 Pesho P. [email protected]
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.
Colin L. 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…
Best,
Petar
lol, that’s great!
THANKS A LOT, Colin ! ! !
Have a great weekend
Petar