Reduce usage of LEFT JOIN in queries with include

Question

I'm getting very ugly SQL queries with Rails code like one listed below:

Facility.includes(:type, :owner_building, :delegated_building, keeper_building, :owner_user, :keeper_user).order('users.name ASC').all

It produces:

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

So how can I use LEFT JOIN only for fields on which I have conditions (like ordering) and simple SELECTs for other tables (as includes regularly work when there are no conditions)?

Answer 1

I don't fully understand the question; But do you mean by Ugly the `Select A as B, X as Y'?

If so, then this is Rails (AR) way of mapping the returned values correctly to their corresponding object(s). Any t0_r* will be mapped to a facility object, any t1_r* will be mapped to a building object, etc...

Using select will not help you there.

This query is used by Rails internally, and as far as I remember, other ORMs do the same (Hibernate for instance).