Howto express schema in model for one to many relationship

Rails
1

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

2

On Aug 1, 11:23 am, rrevo [email protected] wrote:

) ENGINE=InnoDB;
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

3

Robin F. wrote:

) ENGINE=InnoDB;
position int(11) NOT NULL
class D < ActiveRecord::Base
ps table named d_id. The models would look as follows:
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

4

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