title: Ruby on Rails - How to join two tables?
body: <p>I have two tables (subjects and pages) in one-to-many relations.  I want to add criterias from subjects as well pages to parse a sql, but the progress has been very slow and often times running into problems.  I'm brand new in rails, please help.</p>
  
  <pre><code>class Subject &lt; ActiveRecord::Base
    has_many :pages
  end
  
  class Page &lt; ActiveRecord::Base
    belongs_to :subject 
  end
  </code></pre>
  
  <p>sample data in subjects, listed three columns below:</p>
  
  <pre><code>id  name    level
  1   'Math'  1
  6   'Math'  2
  ...
  </code></pre>
  
  <p>Sample data in pages, listed columns below:</p>
  
  <pre><code>id  name                    subject_id
  --  --------------------    ----------
  2   Addition                1
  4   Subtraction             1
  5   Simple Multiplication   6
  6   Simple Division         6
  7   Hard Multiplication     6
  8   Hard Division           6
  9   Elementary Divsion      1
  </code></pre>
  
  <p>Given that I don't know the subject.id, I only know the subject name and level, and page name.  Here is the sql I want to generate (or something similar that would achieve the same result):</p>
  
  <pre><code>select subjects.id, subjects.name, pages.id, pages.name from subjects, pages
   where subjects.id = pages.subject_id
     and subjects.name = 'Math'
     and subjects.level = '2'
     and pages.name like '%Division'  ;
  </code></pre>
  
  <p>I expect to get two rows in the result:</p>
  
  <pre><code>subjects.id     subjects.name   pages.id    pages.name 
  -----------     -------------   --------    -----------
  6               Math            6           Simple Division
  6               Math            8           Hard Division
  </code></pre>
  
  <p>This is a very simple sql, but I have not been able to get want I wanted in rails.  </p>
  
  <pre><code>Here is my rails console:  
  
  &gt;&gt; subject = Subject.where(:name =&gt; 'Math', :level =&gt; 2)
    Subject Load (0.4ms)  SELECT `subjects`.* FROM `subjects` WHERE `subjects`.`name` = 'Math' AND `subjects`.`level` = 2
  [#&lt;Subject id: 6, name: "Math", position: 1, visible: true, created_at: "2011-12-17 04:25:54", updated_at: "2011-12-17 04:25:54", level: 2&gt;]
  &gt;&gt; 
  &gt;&gt; subject.joins(:pages).where(['pages.name LIKE ?', '%Division'])
    Subject Load (4.2ms)  SELECT `subjects`.* FROM `subjects` INNER JOIN `pages` ON `pages`.`subject_id` = `subjects`.`id` WHERE `subjects`.`name` = 'Math' AND `subjects`.`level` = 2 AND (pages.name LIKE '%Division')
  [#&lt;Subject id: 6, name: "Math", position: 1, visible: true, created_at: "2011-12-17 04:25:54", updated_at: "2011-12-17 04:25:54", level: 2&gt;, #&lt;Subject id: 6, name: "Math", position: 1, visible: true, created_at: "2011-12-17 04:25:54", updated_at: "2011-12-17 04:25:54", level: 2&gt;]
  &gt;&gt; 
  &gt;&gt; subject.to_sql
  "SELECT `subjects`.* FROM `subjects`  WHERE `subjects`.`name` = 'Math' AND `subjects`.`level` = 2"
  &gt;&gt; subject.size
  1
  &gt;&gt; subject.class
  ActiveRecord::Relation
  </code></pre>
  
  <hr>
  
  <p>1st statement: subject = Subject.where(:name => 'Math', :level => 2)
  2nd statement: subject.joins(:pages).where(['pages.name LIKE ?', '%Division'])</p>
  
  <p>Questions:</p>
  
  <ol>
  <li>the results of the chained sql really returns two rows, but subject.size says only 1? </li>
  <li>How do I tell it to return columns from :pages as well?</li>
  <li>Why subject.to_sql still shows the sql from statement 1 only, why did it not include the chained sql from statement 2?</li>
  <li>Essentially, what do I need to write the statements differently to parse the sql as listed above (or achieve the same result)?</li>
  </ol>
  
  <p>Many thanks.</p>