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! -- Posted via http://www.ruby-forum.com/.
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
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. -- Posted via http://www.ruby-forum.com/.
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 :) -- Posted via http://www.ruby-forum.com/.
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 -- Posted via http://www.ruby-forum.com/.
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 -- Posted via http://www.ruby-forum.com/.
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
On 4/19/06, David <null@example.com> wrote:> 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
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>