CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3activerecord
kmc
kmc

Reputation: 658

How to includes() another table in Rails so that I get child objects

Had a really difficult time titling this one - please adjust as you see fit.

Essentially, I would like to do this in Rails:

SELECT airports.*, observations.*
FROM airports
LEFT OUTER JOIN observations ON airports.id = observations.airport_id
WHERE airports.latitude > 51 AND airports.latitude < 52 AND airports.longitude > 0.5 AND airports.longitude < 2
AND (observations.month = 10 OR observations.month IS NULL)

This gives me a nice result with a list of airports in a bounding box and their observations, whether they have any or not.

How can I do that in Rails 3? Whatever I try I only get airports in the resulting array, with no observations at all. I can get rails to form the SQL by doing an includes and specifying a condition, like so:

Airport.includes(:observations)
.where('observations.month = 10 OR observations.month IS NULL')
.where("airports.latitude > 51 AND airports.latitude < 52 AND airports.longitude > 0.5 AND airports.longitude < 2")

This is great, it gives me sql which does the same as what I wrote above:

SELECT `airports`.`id` AS t0_r0, `airports`.`name` AS t0_r1, `airports`.`city` AS t0_r2,
`airports`.`country` AS t0_r3, `airports`.`iata` AS t0_r4, `airports`.`icao` AS t0_r5, 
`airports`.`latitude` AS t0_r6, `airports`.`longitude` AS t0_r7, `airports`.`altitude` AS 
t0_r8, `airports`.`timezone` AS t0_r9, `airports`.`dst` AS t0_r10, `airports`.`created_at`
 AS t0_r11, `airports`.`updated_at` AS t0_r12, `observations`.`id` AS t1_r0, 
`observations`.`airport_id` AS t1_r1, `observations`.`month` AS t1_r2, 
`observations`.`temperature_max` AS t1_r3, `observations`.`temperature_min` AS t1_r4, 
`observations`.`chance_of_rain` AS t1_r5, `observations`.`rain_avg` AS t1_r6, 
`observations`.`dew_point` AS t1_r7, `observations`.`created_at` AS t1_r8, 
`observations`.`updated_at` AS t1_r9, `observations`.`fortnight` AS t1_r10 FROM `airports`
 LEFT OUTER JOIN `observations` ON `observations`.`airport_id` = `airports`.`id` WHERE 
(observations.month = 10 OR observations.month IS NULL) AND (airports.latitude > 51 AND 
airports.latitude < 52 AND airports.longitude > 0.5 AND airports.longitude < 2)

But the record result is disappointing. No observations (Southend should have observations):

[#<Airport id: 14343, name: "Southend", city: "Southend", country: "United Kingdom",
iata: "SEN", icao: "EGMC", latitude: 51.5714, longitude: 0.695556, altitude: 49, 
timezone: 0, dst: "E", created_at: "2011-12-08 11:27:28", updated_at: "2011-12-08 
11:27:28">, #<Airport id: 14345, name: "Manston", city: "Manston", country: "United 
Kingdom", iata: "MSE", icao: "EGMH", latitude: 51.3422, longitude: 1.34611, altitude: 
178, timezone: 0, dst: "E", created_at: "2011-12-08 11:27:28", updated_at: "2011-12-08 
11:27:28">, #<Airport id: 15292, name: "Ashford", city: "Lympne", country: "United 
Kingdom", iata: "LYM", icao: "EGMK", latitude: 51.0833, longitude: 1.01667, altitude: 
351, timezone: 0, dst: "E", created_at: "2011-12-08 11:27:29", updated_at: "2011-12-08 
11:27:29">, #<Airport id: 20740, name: "Ferry Port", city: "Dover", country: "United 
Kingdom", iata: nil, icao: "\\N", latitude: 51.1269, longitude: 1.33975, altitude: 6, 
timezone: 0, dst: "E", created_at: "2011-12-08 11:27:38", updated_at: "2011-12-08 11:27:38">]

It seems I'm missing something fundamental here. Any clues? Thanks.

Upvotes: 1

Views: 3612

Answers (1)

Cody Swann
Cody Swann

Reputation: 677

The resulting objects won't appear in the result set, but are eagerly loaded to avoid querying for the objects when needed.

http://guides.rubyonrails.org/active_record_querying.html

Active Record lets you specify in advance all the associations that are going to be loaded. This is possible by specifying the includes method of the Model.find call. With includes, Active Record ensures that all of the specified associations are loaded using the minimum possible number of queries.

clients = Client.includes(:address).limit(10)

clients.each do |client|
  puts client.address.postcode
end

The above code will execute just 2 queries, as opposed to 11 queries in the previous case:

SELECT * FROM clients LIMIT 10
SELECT addresses.* FROM addresses
  WHERE (addresses.client_id IN (1,2,3,4,5,6,7,8,9,10))

Upvotes: 1

Related Questions