mysql– 减少使用include的查询中LEFT JOIN的使用

概述我正在使用Rails代码获得非常难看的SQL查询,如下所示：Facility.includes(:type, :owner_building, :delegated_building, keeper_building, :owner_user, :keeper_user).order('users.name ASC').all 它产生：SELECT `fac

我正在使用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_r4FROM `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,它在连接方法中支持外连接. 总结

以上是内存溢出为你收集整理的mysql – 减少使用include的查询中LEFT JOIN的使用全部内容，希望文章能够帮你解决mysql – 减少使用include的查询中LEFT JOIN的使用所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。