join,include

mysql, 是沒有include 這種東西的
在rails 下join 如
<br />#用join 就會把所有的relation table 全都join 起來變成大table<br />AdFile.find(:all, :joins => :locations)<br /><br />SELECT `ad_files`.* FROM `ad_files` INNER JOIN `file_locations` ON (`ad_files`.`id` = `file_locations`.`ad_file_id`) INNER JOIN `locations` ON (`locations`.`id` = `file_locations`.`location_id`)<br /><br />#如果是用include 意義是一樣, 可是rails 會幫妳把他分成幾個sql 去做, 這樣就不會因為join 的table 太大會痛了<br />AdFile.find(:all, :include => :locations)<br /><br /> SELECT * FROM `ad_files` <br /> SELECT `file_locations`.* FROM `file_locations` WHERE (`file_locations`.ad_file_id IN (17,18,19))<br /> SELECT * FROM `locations` WHERE (`locations`.`id` IN (1,2,3)) <br />
rails 用join 就是用inner join 弄成一整個大的object, association 就沒了
用include 有的時候因為條件裡直接需要用到另個table 的條件, 是用left join 但仍維持 association 的關係
<br />AdFile.find(:all, :joins => :locations, :conditions => {:locations => {:code => 'goyou_top'}}).first.locations<br /><br />SELECT `ad_files`.`id` AS t0_r0, `ad_files`.`buyer_id` AS t0_r1, `ad_files`.`render_num` AS t0_r2, `ad_files`.`file_type` AS t0_r3, `ad_files`.`filename` AS t0_r4, `ad_files`.`redirect_url` AS t0_r5, `ad_files`.`start_at` AS t0_r6, `ad_files`.`expire_at` AS t0_r7, `ad_files`.`created_at` AS t0_r8, `ad_files`.`active` AS t0_r9, `locations`.`id` AS t1_r0, `locations`.`code` AS t1_r1, `locations`.`height` AS t1_r2, `locations`.`width` AS t1_r3 FROM `ad_files` LEFT OUTER JOIN `file_locations` ON (`ad_files`.`id` = `file_locations`.`ad_file_id`) LEFT OUTER JOIN `locations` ON (`locations`.`id` = `file_locations`.`location_id`) WHERE (locations.code = 'goyou_top')<br />#如果妳就算不取locations 會發現sql 也一樣<br /><br /><br />AdFile.find(:all, :joins => :locations, :conditions => {:locations => {:code => 'goyou_top'}}).first.locations<br /><br /> SELECT `ad_files`.* FROM `ad_files` INNER JOIN `file_locations` ON (`ad_files`.`id` = `file_locations`.`ad_file_id`) INNER JOIN `locations` ON (`locations`.`id` = `file_locations`.`location_id`) WHERE (`locations`.`code` = 'goyou_top')<br /><br />SELECT `locations`.* FROM `locations` INNER JOIN file_locations ON locations.id = file_locations.location_id WHERE ((`file_locations`.ad_file_id = 18))<br />#會發現location 根本是重新在join 一次去取的<br />#但其實要取location 的資料可以直接在join 回來的object 直接拿就可以了<br />

另外rails 2.2 可以把 join 寫的high 一點了
Easy Join Table Conditions<br />class Article < ActiveRecord::Base<br /> belongs_to :user<br />end<br /><br />class User < ActiveRecord::Base<br /> has_many :articles<br />end<br /><br /># Get all the users that have published articles<br />User.find(:all, :joins => :article,<br /> :conditions => ["articles.published = ?", true])<br /><br /># high 一點的寫法<br /># Get all the users that have published articles <br />User.find(:all, :joins => :article,<br /> :conditions => { :articles => { :published => true } })<br />