Forum: Ruby on Rails Concatenated key

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.
244d562a000503dcd35a61ba6acc026c?d=identicon&s=25 Guillaume Filion (Guest)
on 2006-05-18 20:36
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:in
`validate_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)) 
---

Any idea on how to do it?

Thanks a lot,
GFK's
24d2f8804e6bb4b7ea6bd11e0a586470?d=identicon&s=25 Jeremy Kemper (Guest)
on 2006-05-18 21:09
(Received via mailing list)
On May 18, 2006, at 11:36 AM, Guillaume Filion 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
244d562a000503dcd35a61ba6acc026c?d=identicon&s=25 Guilllaume Filion (Guest)
on 2006-05-19 00:45
Jeremy Kemper 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
24d2f8804e6bb4b7ea6bd11e0a586470?d=identicon&s=25 Jeremy Kemper (Guest)
on 2006-05-19 01:11
(Received via mailing list)
On May 18, 2006, at 3:45 PM, Guilllaume Filion wrote:

> Jeremy Kemper 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
D5145c421cd25af6fa577c15219add90?d=identicon&s=25 unknown (Guest)
on 2006-05-19 03:20
(Received via mailing list)
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
244d562a000503dcd35a61ba6acc026c?d=identicon&s=25 Guillaume Filion (Guest)
on 2006-05-19 17:13
Jeremy Kemper 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 %>
<p><% @Sectors.each do |Sector| %>
<input type="checkbox" name="<%= Sector.SECT_NAME %>"<%
@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)
%><%= " checked" if (@nb == 1)
%>><label for="<%=Sector.SECT_NAME%>"><%=Sector.SECT_NAME%></label>
<% end %></p>
<p><%= submit_tag "Modify" %></p>
<%= 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
244d562a000503dcd35a61ba6acc026c?d=identicon&s=25 Guillaume Filion (Guest)
on 2006-05-19 17:24
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
24d2f8804e6bb4b7ea6bd11e0a586470?d=identicon&s=25 Jeremy Kemper (Guest)
on 2006-05-19 20:18
(Received via mailing list)
On May 19, 2006, at 8:24 AM, Guillaume Filion 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
244d562a000503dcd35a61ba6acc026c?d=identicon&s=25 Guillaume Filion (Guest)
on 2006-05-19 22:23
Jeremy Kemper 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
This topic is locked and can not be replied to.