max max - 1 month ago 6
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

stations#index
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)
eager_load(:observations).where(
observations: { id: Observation.pluck_from_each_station(limit) }
)
end
end


Observation.pluck_from_each_station
returns an array of ids. The
observations
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
end


From my understanding a
LEFT OUTER JOIN
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"
LEFT OUTER JOIN
"observations"
ON "observations"."station_id" = "stations"."id"
WHERE "observations"."id" IN (450, 500, 550, 600, 650, 700, 750, 800);

Answer

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

eager_load(:observations).where(
  '"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.