max max - 1 year ago 66
SQL Question

Rails eager_load with conditions on association

I have a Rails application which has Stations (weather stations) and Observations. The app shows many weather stations on a map with the current wind speed and direction.

I have a method which is used on the

method which selects the stations and joins the latest observation per station.

class Station < ActiveRecord::Base
has_many :observations
def self.with_observations(limit = 1)
observations: { id: Observation.pluck_from_each_station(limit) }

returns an array of ids. The
table contains many thousands of rows so this is necessary to keep rails from eager loading thousands of records.

This method should return all the stations - whether the have any observations or not. However this is currently not the case.

it "includes stations that have no observations" do
new_station = create(:station)
stations = Station.with_observations(2)
expect(stations).to include new_station # fails

From my understanding a
should return all rows wether the there are any results in the joined table or not. Why is this not working as expected?

This is an example of the SQL generated:

SELECT "stations"."id" AS t0_r0,
"stations"."name" AS t0_r1,
"stations"."hw_id" AS t0_r2,
"stations"."latitude" AS t0_r3,
"stations"."longitude" AS t0_r4,
"stations"."balance" AS t0_r5,
"stations"."timezone" AS t0_r6,
"stations"."user_id" AS t0_r7,
"stations"."created_at" AS t0_r8,
"stations"."updated_at" AS t0_r9,
"stations"."slug" AS t0_r10,
"stations"."speed_calibration" AS t0_r11,
"stations"."firmware_version" AS t0_r12,
"stations"."gsm_software" AS t0_r13,
"stations"."description" AS t0_r14,
"stations"."sampling_rate" AS t0_r15,
"stations"."status" AS t0_r16,
"observations"."id" AS t1_r0,
"observations"."station_id" AS t1_r1,
"observations"."speed" AS t1_r2,
"observations"."direction" AS t1_r3,
"observations"."max_wind_speed" AS t1_r4,
"observations"."min_wind_speed" AS t1_r5,
"observations"."temperature" AS t1_r6,
"observations"."created_at" AS t1_r7,
"observations"."updated_at" AS t1_r8,
"observations"."speed_calibration" AS t1_r9
FROM "stations"
ON "observations"."station_id" = "stations"."id"
WHERE "observations"."id" IN (450, 500, 550, 600, 650, 700, 750, 800);

Answer Source

I think that happens, because u are excluding records where "observations"."id" is null after the left join:

  '"observations"."id" is null or "observations"."id" in (?)', Observation.pluck_from_each_station(limit)

It is logically the same as left join on two conditions, but as rails doesn't have this feature you can work around it using the where clause.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download