Forum: Ruby on Rails database question

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
F0cb673a145d7630718c4729297f60df?d=identicon&s=25 Pesho Petrov (pesho318i)
on 2009-03-05 14:19
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!
5f94b9b346c2aa648a80bc259978e5bc?d=identicon&s=25 Colin Law (Guest)
on 2009-03-05 16:36
(Received via mailing list)
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@andreas-s.net>
F0cb673a145d7630718c4729297f60df?d=identicon&s=25 Pesho Petrov (pesho318i)
on 2009-03-05 17:59
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



:)
5f94b9b346c2aa648a80bc259978e5bc?d=identicon&s=25 Colin Law (Guest)
on 2009-03-05 21:19
(Received via mailing list)
2009/3/5 Pesho Petrov <rails-mailing-list@andreas-s.net>

>  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?
F0cb673a145d7630718c4729297f60df?d=identicon&s=25 Pesho Petrov (pesho318i)
on 2009-03-05 22:47
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!
5f94b9b346c2aa648a80bc259978e5bc?d=identicon&s=25 Colin Law (Guest)
on 2009-03-05 23:09
(Received via mailing list)
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@andreas-s.net>
F0cb673a145d7630718c4729297f60df?d=identicon&s=25 Pesho Petrov (pesho318i)
on 2009-03-05 23:33
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?

:)
5f94b9b346c2aa648a80bc259978e5bc?d=identicon&s=25 Colin Law (Guest)
on 2009-03-06 09:42
(Received via mailing list)
2009/3/5 Pesho Petrov <rails-mailing-list@andreas-s.net>

> > 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
F0cb673a145d7630718c4729297f60df?d=identicon&s=25 Pesho Petrov (pesho318i)
on 2009-03-06 11:00
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...

Best,
Petar
5f94b9b346c2aa648a80bc259978e5bc?d=identicon&s=25 Colin Law (Guest)
on 2009-03-06 11:25
(Received via mailing list)
2009/3/6 Pesho Petrov <rails-mailing-list@andreas-s.net>

> 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.
F0cb673a145d7630718c4729297f60df?d=identicon&s=25 Pesho Petrov (pesho318i)
on 2009-03-06 11:31
lol, that's great!

THANKS A LOT, Colin ! ! !

Have a great weekend :)
Petar
This topic is locked and can not be replied to.