How to access joined table attributes in Rails3 ?

Question

I am having trouble accessing the attributes of a join in Rails3.

There is two models/tables : Place and Address. One place can have many addresses (i.e. a specific street address, a "corner of" address, etc.). For historical reasons, the tables do not follow standard Rails conventions:

class Place < ActiveRecord::Base
 set_table_name :PLACE
 set_primary_key :PLACE_ID
 has_many :addresses, :class_name => "Address", :foreign_key => :PLACE_ID
end

and

class Address < ActiveRecord::Base
 set_table_name :ADDRESS
 set_primary_key :ADDRESS_ID
 belongs_to :place, :foreign_key => "PLACE_ID"
end

I am trying to get all the addresses for one particular place:

pa = Place.joins(:addresses).where(:place_id => 68)

The SQL that is generated looks fine:

pa.to_sql

"SELECT [PLACE].* FROM [PLACE] INNER JOIN [ADDRESS] ON [ADDRESS].[PLACE_ID] = [PLACE].[PLACE_ID] WHERE ([PLACE].[place_id] = 68)"

The returned relation also has the right size, as that particular place has 6 addresses associated with it:

irb(main):050:0> pa.size
=> 6

However, the returned relation pa only contains the attributes of the Place model, it doesn't contain any attributes of the Address model.

Pre Rails3 I used to do a find_by_sql and could access the attributes of the two joined table easily in the returned Hash, however I simply cannot get Rails3 to reveal to me the attributes from the joined Address table.

I must be missing something very basic here - anyone care to point it out to me ?

Answer 1

I am trying to get all the addresses for one particular place

In Rails you can just do:

pa = Place.where(:place_id => 68)
addresses_for_pa = pa.addresses # all the addresses for one particular place

# do something with each address:
addresses_for_pa.each do |address|
  # Here you can use address.street etc.. 
end

Answer 2

You're looking for includes instead of joins. This will do exactly what you want.

pa = Place.includes(:addresses).where(:place_id => 68)

If you want to order by address.street_name in your comment to captaintokyo's answer. You can do then add an order like this:

pa = Place.includes(:addresses).where(:place_id => 68).order(:addresses => :street_name)

The difference between the two includes and joins is that joins will join the supplied models to the produced query for the sake matching in a where clause. As opposed to includes which, in addition to adding joining the model, will also include that model's fields in the select statement.

So to recap:

Place.includes(:addresses).where(:place_id => 68)

produces this:

"SELECT [PLACE].*, [ADDRESS].* FROM [PLACE] INNER JOIN [ADDRESS] ON [ADDRESS].[PLACE_ID] = [PLACE].[PLACE_ID] WHERE ([PLACE].[place_id] = 68)"

while

Place.joins(:addresses).where(:place_id => 68)

produces this:

"SELECT [PLACE].* FROM [PLACE] INNER JOIN [ADDRESS] ON [ADDRESS].[PLACE_ID] = [PLACE].[PLACE_ID] WHERE ([PLACE].[place_id] = 68)"