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

Question

我使用 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(包括在没有条件的情况下正常工作)？

Answer 1

我不完全理解这个问题；但是，您所说的丑陋是指“选择 A 作为 B，X 作为 Y”吗？

如果是这样，那么这就是 Rails (AR) 将返回值正确映射到相应对象的方法。任何 t0_r* 都将映射到设施对象，任何 t1_r* 都将映射到建筑物对象，等等...

使用 select 不会帮助你。

这个查询由 Rails 内部使用，据我所知，其他 ORM 也做同样的事情(例如 Hibernate)。