how to select attributes on :join ?

rubyonrails-talk
1

when I use a quart joins on Roles and Profiles models

Role.joins(:profiles).where('profiles.profilable_type' => "Admin")

I get the following generated sql SELECT `roles`.* FROM `roles` INNER JOIN `profiles_roles` ON `profiles_roles`.`role_id` = `roles`.`id` INNER JOIN `profiles` ON `profiles`.`id` = `profiles_roles`.`profile_id` WHERE `profiles`.`profilable_type` = 'Admin'

which gives me all roles attributes only

I should I write my Rails query to get all attributes ?

SELECT `roles`.*, `profiles`.* FROM `roles` INNER JOIN `profiles_roles` ON `profiles_roles`.`role_id` = `roles`.`id` INNER JOIN `profiles` ON `profiles`.`id` = `profiles_roles`.`profile_id` WHERE `profiles`.`profilable_type` = 'Admin'

in this case, I'll have 2 attributes with same name in both tables ( :id and :name ) is there any way to avoid such collision ( using AS ...)

2

If you get the profiles using something like profiles = Profile.where( profileable_type => "Admin") then for each profile in the collection you can do profile.roles to get all the roles for that profile.

Alternatively use :includes( :profiles ) rather than join and specify a where for the the profilable type which will give you a collection of Roles.

Colin

3

If for some reason you need the results of the join, you can do:

Role.joins(:profiles).where('profiles.profilable_type' => "Admin").select('profiles.id, roles.id, profiles.name, profiles.id as p_id') # you can use the sql 'as' in the string

Keep in mind this will return an ActiveRecord::Relation instance, not a set of Role or Profile, so if your plan is to iterate through results, you better do it as Collin instructed