|
我正在使用Rails代码获得非常难看的SQL查询,如下所示：
Facility.includes(:type,:owner_building,:delegated_building,keeper_building,:owner_user,:keeper_user).order('users.name ASC').all
它产生：
SELECT `facilities`.`id` AS t0_r0,`facilities`.`name` AS t0_r1,`facilities`.`brand` AS t0_r2,`facilities`.`desc` AS t0_r3,`facilities`.`type_id` AS t0_r4,`facilities`.`owner_building_id` AS t0_r5,`facilities`.`keeper_building_id` AS t0_r6,`facilities`.`delegated_building_id` AS t0_r7,`facilities`.`owner_user_id` AS t0_r8,`facilities`.`keeper_user_id` AS t0_r9,`buildings`.`id` AS t1_r0,`buildings`.`name` AS t1_r1,`buildings`.`address` AS t1_r2,`buildings`.`created_at` AS t1_r3,`buildings`.`updated_at` AS t1_r4,`buildings`.`comments` AS t1_r5,`delegated_buildings_facilities`.`id` AS t2_r0,`delegated_buildings_facilities`.`name` AS t2_r1,`delegated_buildings_facilities`.`address` AS t2_r2,`delegated_buildings_facilities`.`created_at` AS t2_r3,`delegated_buildings_facilities`.`updated_at` AS t2_r4,`delegated_buildings_facilities`.`comments` AS t2_r5,`keeper_buildings_facilities`.`id` AS t3_r0,`keeper_buildings_facilities`.`name` AS t3_r1,`keeper_buildings_facilities`.`address` AS t3_r2,`keeper_buildings_facilities`.`created_at` AS t3_r3,`keeper_buildings_facilities`.`updated_at` AS t3_r4,`keeper_buildings_facilities`.`comments` AS t3_r5,`users`.`id` AS t4_r0,`users`.`company_id` AS t4_r1,`users`.`building_id` AS t4_r2,`users`.`login` AS t4_r3,`users`.`name` AS t4_r4,`users`.`role` AS t4_r5,`users`.`email` AS t4_r6,`users`.`comments` AS t4_r7,`users`.`crypted_password` AS t4_r8,`users`.`password_salt` AS t4_r9,`users`.`persistence_token` AS t4_r10,`users`.`perishable_token` AS t4_r11,`users`.`login_count` AS t4_r12,`users`.`failed_login_count` AS t4_r13,`users`.`last_request_at` AS t4_r14,`users`.`current_login_at` AS t4_r15,`users`.`last_login_at` AS t4_r16,`users`.`current_login_ip` AS t4_r17,`users`.`last_login_ip` AS t4_r18,`users`.`created_at` AS t4_r19,`users`.`updated_at` AS t4_r20,`keeper_users_facilities`.`id` AS t5_r0,`keeper_users_facilities`.`company_id` AS t5_r1,`keeper_users_facilities`.`building_id` AS t5_r2,`keeper_users_facilities`.`login` AS t5_r3,`keeper_users_facilities`.`name` AS t5_r4,`keeper_users_facilities`.`role` AS t5_r5,`keeper_users_facilities`.`email` AS t5_r6,`keeper_users_facilities`.`comments` AS t5_r7,`keeper_users_facilities`.`crypted_password` AS t5_r8,`keeper_users_facilities`.`password_salt` AS t5_r9,`keeper_users_facilities`.`persistence_token` AS t5_r10,`keeper_users_facilities`.`perishable_token` AS t5_r11,`keeper_users_facilities`.`login_count` AS t5_r12,`keeper_users_facilities`.`failed_login_count` AS t5_r13,`keeper_users_facilities`.`last_request_at` AS t5_r14,`keeper_users_facilities`.`current_login_at` AS t5_r15,`keeper_users_facilities`.`last_login_at` AS t5_r16,`keeper_users_facilities`.`current_login_ip` AS t5_r17,`keeper_users_facilities`.`last_login_ip` AS t5_r18,`keeper_users_facilities`.`created_at` AS t5_r19,`keeper_users_facilities`.`updated_at` AS t5_r20,`facility_types`.`id` AS t6_r0,`facility_types`.`name` AS t6_r1,`facility_types`.`desc` AS t6_r2,`facility_migrations`.`id` AS t7_r0,`facility_migrations`.`building_id` AS t7_r1,`facility_migrations`.`equipment_id` AS t7_r2,`facility_migrations`.`facility_id` AS t7_r3,`facility_migrations`.`created_at` AS t7_r4
FROM `facilities`
LEFT OUTER JOIN`buildings` ON `buildings`.`id` = `facilities`.`owner_building_id`
LEFT OUTER JOIN`buildings` `delegated_buildings_facilities` ON `delegated_buildings_facilities`.`id` = `facilities`.`delegated_building_id`
LEFT OUTER JOIN`buildings` `keeper_buildings_facilities` ON `keeper_buildings_facilities`.`id` = `facilities`.`keeper_building_id`
LEFT OUTER JOIN`users` ON `users`.`id` = `facilities`.`owner_user_id`
LEFT OUTER JOIN`users` `keeper_users_facilities` ON `keeper_users_facilities`.`id` = `facilities`.`keeper_user_id`
LEFT OUTER JOIN`facility_types` ON `facility_types`.`id` = `facilities`.`type_id`
LEFT OUTER JOIN`facility_migrations` ON `facility_migrations`.`facility_id` = `facilities`.`id`
WHERE `facilities`.`id` IN (15,47,16,48,17,49,18,50,19,51,20,52) AND ((1=1)) ORDER BY users.name ASC
那么我怎样才能将LEFT JOIN仅用于我有条件的字段(如排序)和其他表的简单SELECT(因为包括在没有条件时定期工作)？
最佳答案
似乎没有人提到它.有一个名为preload的方法与includes几乎相同,只是它使用单独的查询而不是左连接.
如果您希望左外连接仅用于排序/分组/过滤而不包含在结果中,您可能希望使用squeel gem,它在连接方法中支持外连接.
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
