CODEWITHSUNDEEP
sqlruby-on-railspostgresqlruby-on-rails-4activerecord
max
max

Reputation: 101811

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);

Upvotes: 0

Views: 1670

Answers (1)

potashin
potashin

Reputation: 44581

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.

Upvotes: 1

Related Questions