# Howto express schema in model for one to many relationship

**URL:** <https://www.ruby-forum.com/t/howto-express-schema-in-model-for-one-to-many-relationship/110953>\
**Category:** Rails\
**Created:** [August 1, 2007, 12:23pm UTC](https://www.ruby-forum.com/t/howto-express-schema-in-model-for-one-to-many-relationship/110953 "2007-08-01T12:23:57Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![rrevo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/dec6dc/32.png) [@rrevo](https://www.ruby-forum.com/u/rrevo)\
**Post date:** [August 1, 2007, 12:23pm UTC](https://www.ruby-forum.com/t/howto-express-schema-in-model-for-one-to-many-relationship/110953/1 "2007-08-01T12:23:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rrevo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/dec6dc/32.png) [@rrevo](https://www.ruby-forum.com/u/rrevo)\
**Post date:** [August 1, 2007, 1:38pm UTC](https://www.ruby-forum.com/t/howto-express-schema-in-model-for-one-to-many-relationship/110953/2 "2007-08-01T13:38:12Z")

</div>

On Aug 1, 11:23 am, rrevo [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

---

<div class="post-metadata">

**Author:** ![rrevo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/dec6dc/32.png) [@rrevo](https://www.ruby-forum.com/u/rrevo)\
**Post date:** [August 1, 2007, 2:25pm UTC](https://www.ruby-forum.com/t/howto-express-schema-in-model-for-one-to-many-relationship/110953/3 "2007-08-01T14:25:22Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rrevo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/dec6dc/32.png) [@rrevo](https://www.ruby-forum.com/u/rrevo)\
**Post date:** [August 2, 2007, 3:18am UTC](https://www.ruby-forum.com/t/howto-express-schema-in-model-for-one-to-many-relationship/110953/4 "2007-08-02T03:18:46Z")

</div>

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