i posted this but did not get a response.
i would like to add that i am using postgresql. if i use the primary
key i can do look as below but can not do with another type of key.
do i need to index these?
i also read this but it didn''t lead to the solution.
http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/eb70d8c45a6a3837/864be680bb9977e6?lnk
model user
id (automatic primary)
fname
lname
user_rrid (foreign key) - string "123456"
model email
id (automatic primary)
e_addresss
user_rrid (foreign key) - string "123456"
e_type
i would like to find a user by fname
@user = User.find_by_fname("John")
then use...
@user.email.e_address
@user.email.e_type
i would also like to go the other way. search email based on
e_address
and find the user.
can you please show how the user.rb and email.rb files should be set
up. i have looked thru the docs and googled and everything i try does
not work.
i have tried using foreign_key but it does not work.
thanks.
http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/eb70d8c45a6a3837/864be680bb9977e6?lnk=gst&q=foreign+key#
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
On Tue, 2008-04-01 at 20:51 -0700, rashantha wrote:> i posted this but did not get a response. > > i would like to add that i am using postgresql. if i use the primary > key i can do look as below but can not do with another type of key. > do i need to index these? > > i also read this but it didn''t lead to the solution. > http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/eb70d8c45a6a3837/864be680bb9977e6?lnk > > > model user > id (automatic primary) > fname > lname > user_rrid (foreign key) - string "123456" > model email > id (automatic primary) > e_addresss > user_rrid (foreign key) - string "123456" > e_type > i would like to find a user by fname > @user = User.find_by_fname("John") > then use... > @user.email.e_address > @user.email.e_type > i would also like to go the other way. search email based on > e_address > and find the user. > can you please show how the user.rb and email.rb files should be set > up. i have looked thru the docs and googled and everything i try does > not work. > i have tried using foreign_key but it does not work. > thanks.---- doesn''t sound like a foreign key or index issue at all. model user has_one :email create_table "users", :force => true do |t| t.column "fname", :string, :limit => 25 t.column "email_id", :string, :limit => 25, :null => false end model email belongs_to :user create_table "emails", :force => true do |t| t.column "e_address", :string, :limit => 32 t.column "e_type", :string, :limit => 25 t.column "user_id", :string, :limit => 25, :null => false end Craig --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Tue, 2008-04-01 at 21:03 -0700, Craig White wrote:> On Tue, 2008-04-01 at 20:51 -0700, rashantha wrote: > > i posted this but did not get a response. > > > > i would like to add that i am using postgresql. if i use the primary > > key i can do look as below but can not do with another type of key. > > do i need to index these? > > > > i also read this but it didn''t lead to the solution. > > http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/eb70d8c45a6a3837/864be680bb9977e6?lnk > > > > > > model user > > id (automatic primary) > > fname > > lname > > user_rrid (foreign key) - string "123456" > > model email > > id (automatic primary) > > e_addresss > > user_rrid (foreign key) - string "123456" > > e_type > > i would like to find a user by fname > > @user = User.find_by_fname("John") > > then use... > > @user.email.e_address > > @user.email.e_type > > i would also like to go the other way. search email based on > > e_address > > and find the user. > > can you please show how the user.rb and email.rb files should be set > > up. i have looked thru the docs and googled and everything i try does > > not work. > > i have tried using foreign_key but it does not work. > > thanks. > ---- > doesn''t sound like a foreign key or index issue at all. > > model user > has_one :email > > create_table "users", :force => true do |t| > t.column "fname", :string, :limit => 25 > t.column "email_id", :string, :limit => 25, :null => false > end > > model email > belongs_to :user > > create_table "emails", :force => true do |t| > t.column "e_address", :string, :limit => 32 > t.column "e_type", :string, :limit => 25 > t.column "user_id", :string, :limit => 25, :null => false > end---- sorry...correction both email_id and user_id fields should be :integer, not :string Craig --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Apr 1, 9:03 pm, Craig White <craigwh...-BQ75lA0ptkhBDgjK7y7TUQ@public.gmane.org> wrote:> ---- > doesn''t sound like a foreign key or index issue at all. > > model user > has_one :email > > create_table "users", :force => true do |t| > t.column "fname", :string, :limit => 25 > t.column "email_id", :string, :limit => 25, :null => false > endhere is the problem i don''t want to use the automatic primary key (email_id) or (user_id) i have created user_rrid and email_rrid to have a value of string does this have to be changed to an integer? can''t you use this as a foreign key? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Wed, 2008-04-02 at 07:10 -0700, rashantha wrote:> > > On Apr 1, 9:03 pm, Craig White <craigwh...-BQ75lA0ptkhBDgjK7y7TUQ@public.gmane.org> wrote: > > > ---- > > doesn''t sound like a foreign key or index issue at all. > > > > model user > > has_one :email > > > > create_table "users", :force => true do |t| > > t.column "fname", :string, :limit => 25 > > t.column "email_id", :string, :limit => 25, :null => false > > end > > here is the problem i don''t want to use the automatic primary key > (email_id) or (user_id) > i have created user_rrid and email_rrid to have a value of string > > does this have to be changed to an integer?---- no - a key can be anything unique ----> > can''t you use this as a foreign key?---- I would imagine that you can. I''ll let someone else who''s used foreign key''s answer from here on out since I have never needed to use them. Craig --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
It seems to me that you are confusing several distinct issues.
1. Primary Keys. As distributed, Rails only really works with surrogate
integer keys, however there are plugins that accommodate so-called
''natural'' primary keys. Surrogate key values are assigned by
the DBMS
via a sequencer. Primary keys are automatically given the additional
attribute of an INDEX UNIQUE. By default, Rails defines primary keys to
have the attribute name ''id'' but you can override this in
environment.rb
or in the migration
2. Indexes are DBMS specific processes to speedily retrieve subsets of
data. These can be defined in Rails migrations and are independent of
keys. They can be defined as UNIQUE or not and a single index may span
multiple columns. Indexes are not directly accessible via FIND, the
DBMS planner decides whether or not a search by index makes sense.
3. Foreign Key lookups are constrained to the primary key of the
reference table. Thus they must have (or be cast to) the same data type
as the reference key. Rails does not employ DBMS foreign key
constraints, rather ActiveRecord uses a FIND followed by an INSERT to
enforce this requirement, a practice which can fail under high loads. By
default, Rails considers a foreign key field to possess the pattern
<tablename>_id but this can be modified in environment.rb or overridden
in the model itself.
4. SQL FINDs require neither a key nor an index, only an attribute and a
value. The DBMS planner decides on the basis of the schema and the
statistical data it possesses how best to conduct the search and return
the tuples. It is often the case that with small tables a planner will
simply do a serial search of the entire table in memory rather than
bother with the indices at all.
What I believe that you should do in this case is ignore INDEXES
altogether until you have your design finalized and performance
experience indicates that an INDEX on one of your FIND attributes might
improve response time. The FIND will work with or without an index.
However, if the issue is one of constraining the use of a particular
fname to just one instance then an INDEX UNIQUE is really the only way
to go. (If UNIQUEness is the goal then I have no idea why you would
place such a restriction of fname alone, it seems to me that a composite
key on lname+fname is a far better, albeit not very good, approach).
You should also just stick with the Rails defaults of integer valued
surrogate keys. They work just fine in almost every case even if the
concept may disturb some.
Your migrations should look something like this:
class CreateUsers < ActiveRecord::Migration
def self.up
create_table :users do |t|
t.string :fname, :null => false,
:limit => 40
t.string :lname, :null => false,
:limit => 40
end
# Constrain fname in DBMS
add_index :users, :fname,
:name => :idxU_users_fname,
:unique => true
end
def self.down
remove_index :userss, :name => :idxU_users_fname
drop_table :users
end
end
class CreateEmail < ActiveRecord::Migration
def self.up
create_table :emails do |t|
t.string :e_address, :null => false
t.string :e_type, :null => false
t.integer :user_id, :null => false
end
end
def self.down
drop_table :emails
end
end
The your models look something like this:
class User < ActiveRecord::Base
has_many :emails, :dependent => :destroy
validates_presence_of :fname
validates_presence_of :lname
# note that validates_uniqueness_of does not enforce
# but only checks uniqueness. It is possible to encounter
# a race condition where the entity name is taken between
# the check and the insert.
validates_uniqueness_of :fname
end
class Email < ActiveRecord::Base
belongs_to :user
validates_presence_of :e_address
validates_presence_of :e_type
end
Your FINDs belong in the appropriate controllers, either
users_controller.rb or emails_controller.rb. There are also routes to
consider as email is clearly a nested resource of users. What you end
up with in a controller would look something like this:
...
@emails = @emails.find(:all, :conditions => [ :fname =?, @user.fname
])
...
There are a number of Rails tutorials and screencasts on this. Take a
look at railscasts.com and peepcode.com. Just bear in mind that with
SQL, and therefore in Rails, you can always FIND on any attribute
defined in a table not just those that are designated as being keys or
indices.
HTH
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
markcatley-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
2008-Apr-02 22:30 UTC
Re: Foregin Key PostgreSQL, indexing?
Woops, rails does handle it correctly. The problem was you have to specify foreign key names in lower case or all things turn to custard. Mark On Apr 3, 438 am, James Byrne <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> It seems to me that you are confusing several distinct issues. > > 1. Primary Keys. As distributed, Rails only really works with surrogate > integer keys, however there are plugins that accommodate so-called > ''natural'' primary keys. Surrogate key values are assigned by the DBMS > via a sequencer. Primary keys are automatically given the additional > attribute of an INDEX UNIQUE. By default, Rails defines primary keys to > have the attribute name ''id'' but you can override this in environment.rb > or in the migration > > 2. Indexes are DBMS specific processes to speedily retrieve subsets of > data. These can be defined in Rails migrations and are independent of > keys. They can be defined as UNIQUE or not and a single index may span > multiple columns. Indexes are not directly accessible via FIND, the > DBMS planner decides whether or not a search by index makes sense. > > 3. Foreign Key lookups are constrained to the primary key of the > reference table. Thus they must have (or be cast to) the same data type > as the reference key. Rails does not employ DBMS foreign key > constraints, rather ActiveRecord uses a FIND followed by an INSERT to > enforce this requirement, a practice which can fail under high loads. By > default, Rails considers a foreign key field to possess the pattern > <tablename>_id but this can be modified in environment.rb or overridden > in the model itself. > > 4. SQL FINDs require neither a key nor an index, only an attribute and a > value. The DBMS planner decides on the basis of the schema and the > statistical data it possesses how best to conduct the search and return > the tuples. It is often the case that with small tables a planner will > simply do a serial search of the entire table in memory rather than > bother with the indices at all. > > What I believe that you should do in this case is ignore INDEXES > altogether until you have your design finalized and performance > experience indicates that an INDEX on one of your FIND attributes might > improve response time. The FIND will work with or without an index. > However, if the issue is one of constraining the use of a particular > fname to just one instance then an INDEX UNIQUE is really the only way > to go. (If UNIQUEness is the goal then I have no idea why you would > place such a restriction of fname alone, it seems to me that a composite > key on lname+fname is a far better, albeit not very good, approach). > > You should also just stick with the Rails defaults of integer valued > surrogate keys. They work just fine in almost every case even if the > concept may disturb some. > > Your migrations should look something like this: > > class CreateUsers < ActiveRecord::Migration > def self.up > create_table :users do |t| > t.string :fname, :null => false, > :limit => 40 > t.string :lname, :null => false, > :limit => 40 > end > > # Constrain fname in DBMS > add_index :users, :fname, > :name => :idxU_users_fname, > :unique => true > end > > def self.down > remove_index :userss, :name => :idxU_users_fname > drop_table :users > end > end > > class CreateEmail < ActiveRecord::Migration > def self.up > create_table :emails do |t| > t.string :e_address, :null => false > t.string :e_type, :null => false > t.integer :user_id, :null => false > end > > end > > def self.down > drop_table :emails > end > end > > The your models look something like this: > > class User < ActiveRecord::Base > > has_many :emails, :dependent => :destroy > > validates_presence_of :fname > validates_presence_of :lname > > # note that validates_uniqueness_of does not enforce > # but only checks uniqueness. It is possible to encounter > # a race condition where the entity name is taken between > # the check and the insert. > validates_uniqueness_of :fname > > end > > class Email < ActiveRecord::Base > > belongs_to :user > > validates_presence_of :e_address > validates_presence_of :e_type > > end > > Your FINDs belong in the appropriate controllers, either > users_controller.rb or emails_controller.rb. There are also routes to > consider as email is clearly a nested resource of users. What you end > up with in a controller would look something like this: > > ... > @emails = @emails.find(:all, :conditions => [ :fname =?, @user.fname > ]) > ... > > There are a number of Rails tutorials and screencasts on this. Take a > look at railscasts.com and peepcode.com. Just bear in mind that with > SQL, and therefore in Rails, you can always FIND on any attribute > defined in a table not just those that are designated as being keys or > indices. > > HTH > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Julian Leviston
2008-Apr-03 02:43 UTC
Re: ****[Rails] Re: Foregin Key PostgreSQL, indexing?
In Rails (ActiveRecord), it''s better to use integers as primary and foreign keys. Rails doesn''t seem to like things much if this isn''t the case. Julian. Learn Ruby on Rails! CHECK OUT THE FREE VIDS (LIMITED TIME) NEW VIDEO (#2) OUT NOW! http://sensei.zenunit.com/ On 03/04/2008, at 1:59 AM, Craig White wrote:> > On Wed, 2008-04-02 at 07:10 -0700, rashantha wrote: >> >> >> On Apr 1, 9:03 pm, Craig White <craigwh...-BQ75lA0ptkhBDgjK7y7TUQ@public.gmane.org> wrote: >> >>> ---- >>> doesn''t sound like a foreign key or index issue at all. >>> >>> model user >>> has_one :email >>> >>> create_table "users", :force => true do |t| >>> t.column "fname", :string, :limit => 25 >>> t.column "email_id", :string, :limit => 25, :null => false >>> end >> >> here is the problem i don''t want to use the automatic primary key >> (email_id) or (user_id) >> i have created user_rrid and email_rrid to have a value of string >> >> does this have to be changed to an integer? > ---- > no - a key can be anything unique > ---- >> >> can''t you use this as a foreign key? > ---- > I would imagine that you can. I''ll let someone else who''s used foreign > key''s answer from here on out since I have never needed to use them. > > Craig > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---