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.
Bd3a4af368439803aa37d318f9941a9f?d=identicon&s=25 David (Guest)
on 2006-04-18 16: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!
4daf0b71d5d9a3882e583c0e72eaf5dc?d=identicon&s=25 Alan Francis (Guest)
on 2006-04-18 17: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.
119af50160cabfe1fb6f2f05f5018c64?d=identicon&s=25 James Ludlow (Guest)
on 2006-04-18 17:07
(Received via mailing list)
On 4/18/06, David <null@example.com> 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
Bd3a4af368439803aa37d318f9941a9f?d=identicon&s=25 David (Guest)
on 2006-04-19 09:54
Alan Francis 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 :)
Bd3a4af368439803aa37d318f9941a9f?d=identicon&s=25 David (Guest)
on 2006-04-19 09:56
James Ludlow wrote:
> On 4/18/06, David <null@example.com> 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
Bd3a4af368439803aa37d318f9941a9f?d=identicon&s=25 David (Guest)
on 2006-04-19 10: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
D60346d9f28eb8ac662e103b5f46f9f5?d=identicon&s=25 Chuck Vose (Guest)
on 2006-04-19 17: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.