Linking one model to another


#1

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!


#2

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.

  1. 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.

  1. 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 :slight_smile:

A.


#3

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.

  1. 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.

  1. 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


#4

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

  1. 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 :slight_smile:


#5

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


#6

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


#7

James L. wrote:

On 4/18/06, David removed_email_address@domain.invalid wrote:

  1. 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