Ruby on Rails - How to join two tables?

问题:

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.

class Subject < ActiveRecord::Base
  has_many :pages
end

class Page < ActiveRecord::Base
  belongs_to :subject 
end

sample data in subjects, listed three columns below:

id  name    level
1   'Math'  1
6   'Math'  2
...

Sample data in pages, listed columns below:

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

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):

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'  ;

I expect to get two rows in the result:

subjects.id     subjects.name   pages.id    pages.name 
-----------     -------------   --------    -----------
6               Math            6           Simple Division
6               Math            8           Hard Division

This is a very simple sql, but I have not been able to get want I wanted in rails.

Here is my rails console:  

>> subject = Subject.where(:name => 'Math', :level => 2)
  Subject Load (0.4ms)  SELECT `subjects`.* FROM `subjects` WHERE `subjects`.`name` = 'Math' AND `subjects`.`level` = 2
[#<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>]
>> 
>> 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')
[#<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>, #<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>]
>> 
>> subject.to_sql
"SELECT `subjects`.* FROM `subjects`  WHERE `subjects`.`name` = 'Math' AND `subjects`.`level` = 2"
>> subject.size
1
>> subject.class
ActiveRecord::Relation

---

1st statement: subject = Subject.where(:name => 'Math', :level => 2) 2nd statement: subject.joins(:pages).where(['pages.name LIKE ?', '%Division'])

Questions:

1. the results of the chained sql really returns two rows, but subject.size says only 1?
2. How do I tell it to return columns from :pages as well?
3. Why subject.to_sql still shows the sql from statement 1 only, why did it not include the chained sql from statement 2?
4. Essentially, what do I need to write the statements differently to parse the sql as listed above (or achieve the same result)?

Many thanks.

回答1:

1) ActiveRecord is going to map your query results to objects not arbitrary returned rows, so because you based the query creation off of the Subject class it is looking at your resulting rows and figures out that it is only referring to 1 unique Subject object, so returns just that single Subject instance.

2) The column data is there, but you are working against what ActiveRecord wants to give you, which is objects. If you would rather have Pages returned, then you need to base the creation of the query on the Page class.

3) You didn't save the results of adding the join(:pages)... back into the subject variable. If you did:

subject = subject.joins(:pages).where(['pages.name LIKE ?', '%Division'])

You would get the full query when running subject.to_sql

4) To get page objects you can do something like this, notice that we are basing it off of the Page class:

pages = Page.joins(:subject).where(['subjects.name = ? AND subjects.level = ? AND pages.name LIKE ?', 'Math', 2, '%Division'])

Then to access the subject name from there for the first Page object returned:

pages[0].subject.name

Which because you have the join in the first, won't result in another SQL query. Hope this helps!