Hi all,
I started playing a couple days ago with Rails and it find it very
interesting.
But I’m a bit stuck with making it work with a concatenated primary key.
I want to use my own names for the DB fields and I’m using SQL Server
2005.
My tables look roughly like this:
CREATE TABLE USERS (
USR_ID VARCHAR(25) NOT NULL PRIMARY KEY,
USR_PASS VARCHAR(25)
)
CREATE TABLE SECTOR (
SECT_ID TINYINT NOT NULL PRIMARY KEY,
SECT_NAME VARCHAR(25)
)
CREATE TABLE PERMISSION (
FK_USR_ID VARCHAR(25) NOT NULL,
FK_SECT_ID TINYINT NOT NULL
)
ALTER TABLE PERMISSION
ADD CONSTRAINT PK_PERMISSION
PRIMARY KEY (FK_USR_ID,FK_SECT_ID)
And my models look like this:
class User < ActiveRecord::Base
set_primary_key “USR_ID”
def self.table_name() "USERS" end
has_many :permission
end
class Sector < ActiveRecord::Base
set_primary_key “SECT_ID”
def self.table_name() "SECTOR" end
has_many :permission
end
class Permission < ActiveRecord::Base
set_primary_key “FK_USR_ID,FK_SECT_ID”
def self.table_name() "PERMISSION" end
belongs_to :User
belongs_to :Sector
end
INSERTing permission data is pretty easy:
@perm = Permission.new(“FK_USR_ID” => @user.id, “FK_SECT_ID” =>
@sector.id)
@perm.save
However, SELECTing a permission doesn’t work:
@perm = Permission.find(“FK_USR_ID” => @user.id, “FK_SECT_ID” =>
@sector.id)
gives:
ArgumentError (Unknown key(s): FK_USR_ID, FK_SECT_ID):
c:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.3.1/lib/active_support/core_ext/hash/keys.rb:48:in
assert_valid_keys' c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/base.rb:1323:invalidate_find_options’
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/base.rb:376:in
`find’
@perm = Permission.find(@user.id,@sector.id)
gives:
DBI::DatabaseError: Execute
OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
Incorrect syntax at ‘,’.
HRESULT error code:0x80020009
An exception occured.: SELECT * FROM PERMISSION WHERE
(PERMISSION.FK_USR_ID,FK_SECT_ID IN (‘gfk’,3)) e[0m
Any idea on how to do it?
Thanks a lot,
GFK’s
On May 18, 2006, at 11:36 AM, Guillaume F. wrote:
I started playing a couple days ago with Rails and it find it very
interesting.
But I’m a bit stuck with making it work with a concatenated primary
key.
Hello! and welcome to Rails.
CREATE TABLE SECTOR (
ADD CONSTRAINT PK_PERMISSION
PRIMARY KEY (FK_USR_ID,FK_SECT_ID)
And my models look like this:
class User < ActiveRecord::Base
set_primary_key “USR_ID”
def self.table_name() “USERS” end
has_many :permission
end
class User < ActiveRecord::Base
self.table_name = ‘USERS’
self.primary_key = ‘USR_ID’
has_and_belongs_to_many :sectors, :join_table =>
‘permission’, :foreign_key => ‘FK_USR_ID’, :association_foreign_key
=> ‘FK_SECT_ID’
end
class Sector < ActiveRecord::Base
set_primary_key “SECT_ID”
def self.table_name() “SECTOR” end
has_many :permission
end
class Sector < ActiveRecord::Base
self.table_name = ‘SECTOR’
self.primary_key = ‘SECT_ID’
has_and_belongs_to_many :users, :join_table =>
‘permission’, :foreign_key => ‘FK_SECT_ID’, :association_foreign_key
=> ‘FK_USR_ID’
end
class Permission < ActiveRecord::Base
set_primary_key “FK_USR_ID,FK_SECT_ID”
def self.table_name() “PERMISSION” end
belongs_to :User
belongs_to :Sector
end
Compound primary keys are not supported. Add a surrogate key to the
permission table if you want to represent it with an Active Record
class. It will also allow you to use has_many :through, which is more
flexible & expressive than has_and_belongs_to_many.
Best,
jeremy
Jeremy K. wrote:
Compound primary keys are not supported. Add a surrogate key to the
permission table if you want to represent it with an Active Record
class. It will also allow you to use has_many :through, which is more
flexible & expressive than has_and_belongs_to_many.
Thanks a lot Jeremy, it works now with a surrogate key.
It’s not as elegant as I would have hoped (I have to use find_by_sql
instead of find) but it’s working well.
Thanks again,
GFK’s
On May 18, 2006, at 3:45 PM, Guilllaume Filion wrote:
Jeremy K. wrote:
Compound primary keys are not supported. Add a surrogate key to the
permission table if you want to represent it with an Active Record
class. It will also allow you to use has_many :through, which is more
flexible & expressive than has_and_belongs_to_many.
Thanks a lot Jeremy, it works now with a surrogate key.
It’s not as elegant as I would have hoped (I have to use find_by_sql
instead of find) but it’s working well.
Ah, too bad - tou can almost always use find. Reply with the updated
code and perhaps we can elegant it up 
jeremy
I’m aware this isn’t always possible, but if you can change your table
names and stuff, rails will be able to work things out for you. Your
USERS table should be all lower case (I’m not sure if that’s
required… anyone know for definite? but it is convention), and it’s
primary key should be ‘id’. Same with your SECTORS table. If you don’t
want to specify the table name for the join table, it should be called
sectors_users, but you’ll probably want to leave that as permission,
because it makes more sense. The foreign keys should then be ‘user_id’
and ‘sector_id’. I’ve been using the word should, but of course, you
can do it how you want… it’s just that doing it this way requires
less code.
If you follow these conventions, your model can be as simple as:
class User < ActiveRecord::Base
has_and_belongs_to_many :sectors, :join_table => ‘permission’
end
class Sector < ActiveRecord::Base
has_and_belongs_to_many :users, join_table => ‘permission’
end
-Nathan
unknown wrote:
I’m aware this isn’t always possible, but if you can change your table
names and stuff, rails will be able to work things out for you. […]
I’ve been using the word should, but of course, you
can do it how you want… it’s just that doing it this way requires
less code.
If you follow these conventions, your model can be as simple as:
class User < ActiveRecord::Base
has_and_belongs_to_many :sectors, :join_table => ‘permission’
end
class Sector < ActiveRecord::Base
has_and_belongs_to_many :users, join_table => ‘permission’
end
I don’t know. I’m usually quite pedantic with my database design and I
must admit that I don’t find the DB design proposed by rails especially
interesting. But you make a very good point by showing how simple the
model would be. And since I’m learning rails I should be more open
minded about its way of doing things.
Before I change my whole DB design, could you provide me with an example
on how to create and find a permission using this new model?
Thanks a lot,
GFK’s
Jeremy K. wrote:
On May 18, 2006, at 3:45 PM, Guilllaume Filion wrote:
It’s not as elegant as I would have hoped (I have to use find_by_sql
instead of find) but it’s working well.
Ah, too bad - tou can almost always use find. Reply with the updated
code and perhaps we can elegant it up
Thanks for the offer. Let’s give it a try.
I’ve been playing in ruby/rails for 5 days, so I’m pretty sure that you
can make it better…
Let me explain quickly how it works:
list show a list of users (duh), clicking on a user gives the details
page.
The details pages has a checkbox for each sector, checked if the user
has access to that sector. To do this I’m using the following piece of
(ugly) code:
<%= start_form_tag :action => ‘modify’, :id => @User.id %>
<% @Sectors.each do |Sector| %>
><%=Sector.SECT_NAME%>
<% end %>
<%= submit_tag "Modify" %>
<%= end_form_tag %>
The modify code is in the controller (and quite ugly too):
class UserController < ApplicationController
after_filter :set_charset
layout “standard-layout”
def set_charset
@headers[“Content-Type”] ||= “text/html; charset=iso-8859-1”
end
def list
@Users = User.find_all
end
def details
@User = User.find(@params[“id”])
@Sectors = Sector.find_all
end
def modify
@User = User.find(@params[“id”])
@Sectors = Sector.find_all
@Sectors.each do |Sector|
if (@params[Sector.SECT_NAME]) then
@nb = Permission.count_by_sql(“SELECT COUNT() FROM PERMISSION
WHERE FK_USR_ID=’" + @User.id.to_s + “’ AND FK_SECT_ID=” +
Sector.id.to_s)
if (@nb == 0) then
@perm = Permission.new(“FK_USR_ID” => @User.id, “FK_SECT_ID”
=> Sector.id)
@perm.save
end
else
@nb = Permission.count_by_sql("SELECT COUNT() FROM PERMISSION
WHERE FK_USR_ID=’” + @User.id.to_s + “’ AND FK_SECT_ID=” +
Sector.id.to_s)
if (@nb == 1) then
Permission.find_by_sql(“SELECT * FROM PERMISSION WHERE
FK_USR_ID=’” + @User.id.to_s + “’ AND FK_SECT_ID=” +
Sector.id.to_s).first.destroy
end
end
end
redirect_to :action => ‘details’, :id => @User
end
end
Thanks a lot!
GFK’s
On May 19, 2006, at 8:24 AM, Guillaume F. wrote:
class User < ActiveRecord::Base
interesting. But you make a very good point by showing how simple the
model would be. And since I’m learning rails I should be more open
minded about its way of doing things.
Before I change my whole DB design, could you provide me with an
example
on how to create and find a permission using this new model?
user = User.find(:first)
sector = Sector.find(:first)
Permit
user.sectors << sector
Deny
user.sectors.delete(sector)
Permitted?
user.sectors.include?(sector)
I understand how the database conventions can feel abrasive, but the
benefits are delightful and manyfold.
jeremy
Jeremy K. wrote:
Permit
user.sectors << sector
Deny
user.sectors.delete(sector)
Permitted?
user.sectors.include?(sector)
Wow! Impressive. It’s certainly worth changing the field and table
names.
The code is much better looking now:
def modify
@user = User.find(@params[“id”])
Sector.find_all.each do |sector|
if (@params[sector.SECT_NAME]) then
@user.sectors << sector if (not @user.sectors.include?(sector))
else
@user.sectors.delete(sector) if (@user.sectors.include?(sector))
end
end
redirect_to :action => ‘details’, :id => @user
end
Many thanks for your help and have a good week-end,
GFK’s