Ruby on Rails-如何连接两个表? [英] Ruby on Rails - How to join two tables?
问题描述
我有两个表(主题和页面)处于一对多关系.我想从主题以及页面中添加条件来解析sql，但是进度非常慢，而且经常遇到问题.我是Rails的新手，请帮忙.
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
鉴于我不知道subject.id，所以我只知道主题名称和级别以及页面名称.这是我要生成的sql(或可以达到相同结果的类似内容):
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' ;
我希望结果得到两行:
subjects.id subjects.name pages.id pages.name
----------- ------------- -------- -----------
6 Math 6 Simple Division
6 Math 8 Hard Division
这是一个非常简单的sql，但是我一直无法获得想要的结果.
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
第一个语句:subject = Subject.where(:name =>'Math'，:level => 2) 第二条语句:subject.joins(:pages).where([['pages.name LIKE?'，'％Division'])
1st statement: subject = Subject.where(:name => 'Math', :level => 2) 2nd statement: subject.joins(:pages).where(['pages.name LIKE ?', '%Division'])
问题:
- 链接的sql的结果确实返回两行，但是subject.size只说1吗?
- 如何告诉它也从:pages返回列?
- 为什么subject.to_sql仍然仅显示语句1中的sql，为什么它不包含语句2中的链接的sql?
- 本质上，我需要以不同的方式编写语句来解析上面列出的sql(或获得相同的结果)吗?
非常感谢.
推荐答案
1)ActiveRecord会将您的查询结果映射到对象而不是任意返回的行，因此，因为您基于查询创建Subject类，它正在查看结果行，并发现它仅引用1个唯一的Subject对象，因此仅返回单个Subject实例.
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)列数据已存在，但是您正在与ActiveRecord想要给您的对象(即对象)做对.如果您希望返回Pages，则需要将查询的创建基于Page类.
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)您没有保存将join(:pages)...重新添加到subject变量中的结果.如果您这样做:
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'])
运行subject.to_sql
4)要获取页面对象，您可以执行以下操作，请注意，我们将其基于Page类:
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'])
然后从那里访问返回的第一个Page对象的主题名称:
Then to access the subject name from there for the first Page object returned:
pages[0].subject.name
由于您在第一个连接中，因此不会导致另一个SQL查询.希望这会有所帮助！
Which because you have the join in the first, won't result in another SQL query. Hope this helps!
这篇关于Ruby on Rails-如何连接两个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
