Forum: Ruby on Rails Howto express schema in model for one to many relationship

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
rrevo (Guest)
on 2007-08-01 14:23
(Received via mailing list)
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
Robin F. (Guest)
on 2007-08-01 15:38
(Received via mailing list)
On Aug 1, 11:23 am, rrevo <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
Rahul Revo (Guest)
on 2007-08-01 16:25
(Received via mailing list)
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
Kip (Guest)
on 2007-08-02 05:18
(Received via mailing list)
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
This topic is locked and can not be replied to.