sql, subsquery

http://www.1keydata.com/sql/sql-subquery.html
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html
http://railsforum.com/viewtopic.php?pid=68484

include 有時候太大包了, 這時我們可以用的就是subquery

[ActiveRecord]
User.find(:all, :conditions => "id in (select user_id from statuses where fight = true)")
[Sql]
SELECT * FROM `users` WHERE (id in (select user_id from statuses where fight = true))

[ActiveRecord]
User.update_all("group_nickname = 'iamlakela'","users.id in (select user_id from statuses where fight = true)")
[Sql]
UPDATE `users` SET group_nickname = 'iamlakela' WHERE (users.id in (select user_id from statuses where fight = true))

[ActiveRecord]
User.find(:all, :include => :status, :conditions => {'statuses.fight' => true})
[Sql]
SELECT `users`.`id` AS t0_r0, `users`.`name` AS t0_r1, `users`.`password_salt` AS t0_r2, `users`.`password_hash` AS t0_r3, `users`.`email` AS t0_r4, `users`.`created_at` AS t0_r5, `users`.`cookie_hash` AS t0_r6, `users`.`target_time_now` AS t0_r7, `users`.`reset_password_code` AS t0_r8, `users`.`reset_password_code_until` AS t0_r9, `users`.`yahoo_userhash` AS t0_r10, `users`.`group_id` AS t0_r11, `users`.`group_nickname` AS t0_r12, `users`.`time_zone` AS t0_r13, `statuses`.`id` AS t1_r0, `statuses`.`user_id` AS t1_r1, `statuses`.`fight` AS t1_r2, `statuses`.`state` AS t1_r3, `statuses`.`average` AS t1_r4, `statuses`.`success_rate` AS t1_r5, `statuses`.`continuous_num` AS t1_r6, `statuses`.`num` AS t1_r7, `statuses`.`last_record_created_at` AS t1_r8, `statuses`.`group_join_date` AS t1_r9, `statuses`.`attendance` AS t1_r10, `statuses`.`diff` AS t1_r11 FROM `users` LEFT OUTER JOIN `statuses` ON statuses.user_id = users.id WHERE (`statuses`.`fight` = 1)
[

從sample 就可以看出來差距多大了

ps:update 本來就是update 所有符合條件的object