Hey Jeremy, Thanks for the reply! I hadn't even tried a graph, I may end up going with that...keeps everything nicely organized. The second one works as well -- I didn't realize that :something.* was a valid symbol name, I kept including mine in quotes!
Rob On Jun 23, 6:07 pm, Jeremy Evans <[email protected]> wrote: > On Jun 23, 5:56 pm, Rob Cameron <[email protected]> wrote: > > > > > > > So I've got three tables I'm trying to join and two of them contain > > the same column name. I need both columns in my final dataset. I'm > > having a hard time trying to formulate everything so that these > > aliases come through. Can anyone help? I'll reference the standard > > Album/Artist paradigm and add 'Producer': > > > Album.join(:artists, :id => :artist_id).join(:producers, :id > > => :albums__producer_id) > > => SELECT * FROM `albums` INNER JOIN `artists` ON (`artists`.`id` = > > `albums`.`artist_id`) INNER JOIN `producers` ON (`producers`.`id` = > > `albums`.`producer_id`) > > > But, both the `artists` table and `producers` table contain a column > > `name`. All I want to do is alias those as `artist_name` and > > `producer_name` in my final query. I've tried adding a 'select' along > > with the triple underscore alias, but then that removes the `select *` > > for the albums themselves: > > > Album.join(:artists, :id > > => :artist_id).select(:artists__name___artist_name).join(:producers, :id > > => :albums__producer_id) > > => SELECT `artists.name` AS `artist_name` FROM `albums` INNER JOIN > > `artists` ON (`artists`.`id` = `albums`.`artist_id`) INNER JOIN > > `producers` ON (`producers`.`id` = `albums`.`producer_id`) > > > I've tried adding select_more, but since everything is already > > selected it doesn't add anything! And I can't find a way to add a > > select for `albums`.* back into the query... :albums__* isn't valid > > and 'albums.*' literally inserts a quoted string into the SQL which is > > invalid. > > > Any help would be greatly appreciated, I'm trying to avoid just > > writing straight SQL as that doesn't feel right anymore...Ruby has > > spoiled me. :) > > You have a couple of different options. The easiest would be to use > graph instead of join: > > Album.graph(:artists, {:id => :artist_id}, :join_type=>:inner). > join(:producers, {:id => :albums__producer_id}, :join_type=>:inner) > > That'll return hashes with keys :albums, :artists, and :producers, and > subhashes containing the values of columns in the respective tables. > > If you really want to go the select route, you can leave the joins as > they are and do: > > .select(:albums.*, :artists__name___artist) > > I guess what you were missing was the :albums.* part. > > Also, if you want to select something in addition *, you can use > select_append instead of select_more. > > Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
