Forum: Ruby on Rails Linking one model to another

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.
David (Guest)
on 2006-04-18 18:53
I have a User model and a Thing model.  I want to link users with
things, so I have a migration that creates a permissions table with
user_id and thing_id.  The migration works fine and gives me three
columns, I assume I need the permission's ID so that I can delete it in
future.

Three questions come to mind though, which I have given thought but got
nowhere.

1) Does the permissions table need the ID column?

2) Is there any way in the backend/SQL (or otherwise) to ensure that a
given pair of user_id and thing_id are unique?  Obviously the columns
cannot themselves be unique, because a user may have more than one
permission.

3) What is the easiest way to make a UI to link users with things?
Currently I just have two controllers that are slightly modified from
the scaffold.  Is there a neat way that's friendly and quick for the
end-user to assign users to things that is also fairly easy to code?
I've looked for some examples in existing projects but haven't found
anything yet.

Cheers!
Alan F. (Guest)
on 2006-04-18 19:06
David wrote:
> I have a User model and a Thing model.  I want to link users with
> things, so I have a migration that creates a permissions table with
> user_id and thing_id.  The migration works fine and gives me three
> columns, I assume I need the permission's ID so that I can delete it in
> future.
>
> Three questions come to mind though, which I have given thought but got
> nowhere.
>
> 1) Does the permissions table need the ID column?

No. In fact it shouldn't. you can switch it off in the migration.

>
> 2) Is there any way in the backend/SQL (or otherwise) to ensure that a
> given pair of user_id and thing_id are unique?  Obviously the columns
> cannot themselves be unique, because a user may have more than one
> permission.
>

You can set the combination as a composite primary key constraint.

ALTER TABLE things_users ADD CONSTRAINT PRIMARY KEY ('user_id',
'thing_id') in MySQL In think.

> 3) What is the easiest way to make a UI to link users with things?
> Currently I just have two controllers that are slightly modified from
> the scaffold.  Is there a neat way that's friendly and quick for the
> end-user to assign users to things that is also fairly easy to code?
> I've looked for some examples in existing projects but haven't found
> anything yet.

There's nothing prebuilt.  I'd go with a combo with all users and combo
with all things, in controller do

User.find(params[:user_id]) << Thing.find(params[:thing_id])

As long as User has_and_belongs_to_many :things and vice versa, that
shoudl work :-)

A.
James L. (Guest)
on 2006-04-18 19:07
(Received via mailing list)
On 4/18/06, David <removed_email_address@domain.invalid> wrote:
> I have a User model and a Thing model.  I want to link users with
> things, so I have a migration that creates a permissions table with
> user_id and thing_id.  The migration works fine and gives me three
> columns, I assume I need the permission's ID so that I can delete it in
> future.
>
> Three questions come to mind though, which I have given thought but got
> nowhere.
>
> 1) Does the permissions table need the ID column?

Technically, no.  But it's not going to hurt either.  Personally, I'd
use one.

> 2) Is there any way in the backend/SQL (or otherwise) to ensure that a
> given pair of user_id and thing_id are unique?  Obviously the columns
> cannot themselves be unique, because a user may have more than one
> permission.

Yes.  Check your database documentation.  This is a common thing.

> 3) What is the easiest way to make a UI to link users with things?
> Currently I just have two controllers that are slightly modified from
> the scaffold.  Is there a neat way that's friendly and quick for the
> end-user to assign users to things that is also fairly easy to code?
> I've looked for some examples in existing projects but haven't found
> anything yet.

Assuming you're on Rails 1.1+ take a look at the has_many :through
relationships.  It's exactly what you're describing here.

http://wiki.rubyonrails.com/rails/pages/ThroughAssociations

-- James
David (Guest)
on 2006-04-19 11:54
Alan F. wrote:
> David wrote:
>> 1) Does the permissions table need the ID column?
>
> No. In fact it shouldn't. you can switch it off in the migration.

Thanks, I'll look.  I guess I'll also have to look at routing too, so
that the delete action in the controller can take more than just an ID
:)

>> 2) Is there any way in the backend/SQL (or otherwise) to ensure that a
>> given pair of user_id and thing_id are unique?  Obviously the columns
>> cannot themselves be unique, because a user may have more than one
>> permission.
>
> You can set the combination as a composite primary key constraint.
>
> ALTER TABLE things_users ADD CONSTRAINT PRIMARY KEY ('user_id',
> 'thing_id') in MySQL In think.

Hm, it seems that the following works:

ALTER TABLE permissions ADD UNIQUE ('user_id', 'tool_id')

It seems I would have to use 'execute' in the migration to get this to
work, but wouldn't this break compatibility with other DB backends?  Or
can I make it optional somehow?

Thanks for your help, I've learnt some new SQL :)
David (Guest)
on 2006-04-19 11:56
James L. wrote:
> On 4/18/06, David <removed_email_address@domain.invalid> wrote:
>> 3) What is the easiest way to make a UI to link users with things?
>> Currently I just have two controllers that are slightly modified from
>> the scaffold.  Is there a neat way that's friendly and quick for the
>> end-user to assign users to things that is also fairly easy to code?
>> I've looked for some examples in existing projects but haven't found
>> anything yet.
>
> Assuming you're on Rails 1.1+ take a look at the has_many :through
> relationships.  It's exactly what you're describing here.

Perfect, now I need to work out how to do it in code and arrange the
forms so they are easy to use.

Thanks for your help.

David
David (Guest)
on 2006-04-19 12:16
David wrote:
> ALTER TABLE permissions ADD UNIQUE ('user_id', 'tool_id')
>
> It seems I would have to use 'execute' in the migration to get this to
> work, but wouldn't this break compatibility with other DB backends?  Or
> can I make it optional somehow?

I lie, the wiki page shows how this is possible:

add_index :permissions, [:thing_id, :user_id], :unique

David
Chuck V. (Guest)
on 2006-04-19 19:58
(Received via mailing list)
On the forms you can do something like
collection_select('user', 'thing_id', @things, 'id', 'name')
to do this you have to make a @things in the controller but
collection_select rocks for linking tables together like this.

-Chuck
This topic is locked and can not be replied to.