Hi All,
I''m new to Rails and was confused on how to express a model. I
appreciate any help on this. The schema is as follows -
CREATE TABLE ds (
id int(11) NOT NULL auto_increment,
some_data int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE ps (
id int(11) NOT NULL auto_increment,
some_more_data int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE ds_ps (
d_id int(11) NOT NULL, -- Foreign Key to ds.id
p_id int(11) NOT NULL, -- Foreign Key to ps.id
position int(11) NOT NULL
) ENGINE=InnoDB;
Question 1:
The table ds_ps allows me to have a many to many relationship between
ds and ps. But I want to limit it to a one d has many ps in the model.
Hence through an instance of d, d.ps should give me zero or more ps
which are attached to the d.id in the ds_ps table.
Currently I have achieved something like this by the following code:
class D < ActiveRecord::Base
has_many :ps,
:class_name => "P",
:finder_sql => ''SELECT p.* FROM ps p JOIN ds_ps dp '' +
''ON p.id = dp.p_id AND dp.d_id = #{id} ORDER BY
dp.position''
end
Is this the best/recommend way to model such a requirement?
Question 2:
The ds_ps table has an additional column position which I want to be
available in the P instances as an variable when I navigate through
them through the d instance. Like
d.ps.each do |p|
puts p.position
end
This will not be available when a p instance is obtained
directly like p = P.find(1). p.position could be nil.
Thanks for your help.
Rahul
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
Robin Fisher
2007-Aug-01 11:37 UTC
Re: Howto express schema in model for one to many relationship
On Aug 1, 11:23 am, rrevo <rahulr...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi All, > > I''m new to Rails and was confused on how to express a model. I > appreciate any help on this. The schema is as follows - > > CREATE TABLE ds ( > id int(11) NOT NULL auto_increment, > some_data int(11) NOT NULL, > PRIMARY KEY (`id`) > ) ENGINE=InnoDB; > > CREATE TABLE ps ( > id int(11) NOT NULL auto_increment, > some_more_data int(11) NOT NULL, > PRIMARY KEY (`id`) > ) ENGINE=InnoDB; > > CREATE TABLE ds_ps ( > d_id int(11) NOT NULL, -- Foreign Key to ds.id > p_id int(11) NOT NULL, -- Foreign Key to ps.id > position int(11) NOT NULL > ) ENGINE=InnoDB; > > Question 1: > The table ds_ps allows me to have a many to many relationship between > ds and ps. But I want to limit it to a one d has many ps in the model. > Hence through an instance of d, d.ps should give me zero or more ps > which are attached to the d.id in the ds_ps table. > > Currently I have achieved something like this by the following code: > > class D < ActiveRecord::Base > has_many :ps, > :class_name => "P", > :finder_sql => ''SELECT p.* FROM ps p JOIN ds_ps dp '' + > ''ON p.id = dp.p_id AND dp.d_id = #{id} ORDER BY dp.position'' > end > > Is this the best/recommend way to model such a requirement?If you only want to model a one to many relationship, it is not necessary to have a join table. You should use a foreign key in your ps table named d_id. The models would look as follows: class D < ActiveRecord::Base has_many :ps class P < ActiveRecord::Base belongs_to : d When you create a record for your ps table, you should have the option to add the record in the ds table to which the ps record relates. For example, if I have a topic model and a topic has many questions, when adding a question I specify the id of the topic to which it relates. This then allows me to query topic.questions Also, have you considered using migrations rather than direct SQL queries for your database? Best Regards Robin --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Rahul Revo
2007-Aug-01 12:24 UTC
Re: Howto express schema in model for one to many relationship
Robin Fisher wrote:> On Aug 1, 11:23 am, rrevo <rahulr...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> Hi All, >> >> I''m new to Rails and was confused on how to express a model. I >> appreciate any help on this. The schema is as follows - >> >> CREATE TABLE ds ( >> id int(11) NOT NULL auto_increment, >> some_data int(11) NOT NULL, >> PRIMARY KEY (`id`) >> ) ENGINE=InnoDB; >> >> CREATE TABLE ps ( >> id int(11) NOT NULL auto_increment, >> some_more_data int(11) NOT NULL, >> PRIMARY KEY (`id`) >> ) ENGINE=InnoDB; >> >> CREATE TABLE ds_ps ( >> d_id int(11) NOT NULL, -- Foreign Key to ds.id >> p_id int(11) NOT NULL, -- Foreign Key to ps.id >> position int(11) NOT NULL >> ) ENGINE=InnoDB; >> >> Question 1: >> The table ds_ps allows me to have a many to many relationship between >> ds and ps. But I want to limit it to a one d has many ps in the model. >> Hence through an instance of d, d.ps should give me zero or more ps >> which are attached to the d.id in the ds_ps table. >> >> Currently I have achieved something like this by the following code: >> >> class D < ActiveRecord::Base >> has_many :ps, >> :class_name => "P", >> :finder_sql => ''SELECT p.* FROM ps p JOIN ds_ps dp '' + >> ''ON p.id = dp.p_id AND dp.d_id = #{id} ORDER BY dp.position'' >> end >> >> Is this the best/recommend way to model such a requirement? > > If you only want to model a one to many relationship, it is not > necessary to have a join table. You should use a foreign key in your > ps table named d_id. The models would look as follows: > > class D < ActiveRecord::Base > has_many :ps > > class P < ActiveRecord::Base > belongs_to : d > > When you create a record for your ps table, you should have the option > to add the record in the ds table to which the ps record relates. For > example, if I have a topic model and a topic has many questions, when > adding a question I specify the id of the topic to which it relates. > This then allows me to query topic.questionsWhat you have described is the simple case of one to many relationships where the Foreign key of D is kept in each P row. I needed to model this behaviour with all the other goodies like you mentioned but on the join table. The schema cannot be changed but the model needs to be constructed in such a fashion. The finder_sql seems to work for selects. But I am not sure if it is the best approach.> Also, have you considered using migrations rather than direct SQL > queries for your database?I am using rails migrations as well. Just felt more comfortable asking the question in raw SQL. Thanks Rahul --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Rahul,
Perhaps something like:
class ds
has_many :ds_ps
has_many :ps :though => ds_ps
Then you can do
ds.ds_ps[1].position = 33
Or
ds.ps.each { |x| puts x.some_more_data }
ds.ps.find(:conditions => "something in ps constrainted by ds")
In this last example you can have conditions based upon both the ps
table and the ps_ds table since ActiveRecord will put the join into
the underlying SQL so you can:
ds.ps.find(:conditions => "ds_ps.position = 3")
Cheers, --Kip
Cheers, --Kip
On Aug 1, 8:24 pm, Rahul Revo
<rahulr...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> Robin Fisher wrote:
> > On Aug 1, 11:23 am, rrevo
<rahulr...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >> Hi All,
>
> >> I''m new to Rails and was confused on how to express a
model. I
> >> appreciate any help on this. The schema is as follows -
>
> >> CREATE TABLE ds (
> >> id int(11) NOT NULL auto_increment,
> >> some_data int(11) NOT NULL,
> >> PRIMARY KEY (`id`)
> >> ) ENGINE=InnoDB;
>
> >> CREATE TABLE ps (
> >> id int(11) NOT NULL auto_increment,
> >> some_more_data int(11) NOT NULL,
> >> PRIMARY KEY (`id`)
> >> ) ENGINE=InnoDB;
>
> >> CREATE TABLE ds_ps (
> >> d_id int(11) NOT NULL, -- Foreign Key to ds.id
> >> p_id int(11) NOT NULL, -- Foreign Key to ps.id
> >> position int(11) NOT NULL
> >> ) ENGINE=InnoDB;
>
> >> Question 1:
> >> The table ds_ps allows me to have a many to many relationship
between
> >> ds and ps. But I want to limit it to a one d has many ps in the
model.
> >> Hence through an instance of d, d.ps should give me zero or more
ps
> >> which are attached to the d.id in the ds_ps table.
>
> >> Currently I have achieved something like this by the following
code:
>
> >> class D < ActiveRecord::Base
> >> has_many :ps,
> >> :class_name => "P",
> >> :finder_sql => ''SELECT p.* FROM ps p JOIN ds_ps dp
'' +
> >> ''ON p.id = dp.p_id AND dp.d_id = #{id} ORDER BY
dp.position''
> >> end
>
> >> Is this the best/recommend way to model such a requirement?
>
> > If you only want to model a one to many relationship, it is not
> > necessary to have a join table. You should use a foreign key in your
> > ps table named d_id. The models would look as follows:
>
> > class D < ActiveRecord::Base
> > has_many :ps
>
> > class P < ActiveRecord::Base
> > belongs_to : d
>
> > When you create a record for your ps table, you should have the option
> > to add the record in the ds table to which the ps record relates. For
> > example, if I have a topic model and a topic has many questions, when
> > adding a question I specify the id of the topic to which it relates.
> > This then allows me to query topic.questions
>
> What you have described is the simple case of one to many relationships
> where the Foreign key of D is kept in each P row. I needed to model this
> behaviour with all the other goodies like you mentioned but on the join
> table. The schema cannot be changed but the model needs to be
> constructed in such a fashion.
>
> The finder_sql seems to work for selects. But I am not sure if it is the
> best approach.
>
> > Also, have you considered using migrations rather than direct SQL
> > queries for your database?
>
> I am using rails migrations as well. Just felt more comfortable asking
> the question in raw SQL.
>
> Thanks
>
> Rahul
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---