Database question

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

:slight_smile:

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?

:slight_smile:

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 :slight_smile:
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 :slight_smile:
Petar

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs