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.
