Howto express schema in model for one to many relationship

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

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

Robin Fisher 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

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