我有一个Rails应用程序,它有站点(气象站)和观测站。该应用程序在地图上显示了许多气象站当前的风速和方向。
我有一种方法用于stations#index方法,它选择站点并加入每个站点的最新观测。
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
endObservation.pluck_from_each_station返回一个ids数组。observations表包含数千行,因此为了防止rails加载数千条记录,这是必要的。
这种方法应该返回所有的台站--无论是否有任何观测。然而,目前情况并非如此。
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据我理解,LEFT OUTER JOIN应该返回所有行为什么这不像预期的那样起作用?
这是生成的SQL的一个示例:
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);发布于 2016-10-30 13:52:48
我认为会发生这种情况,因为您排除了"observations"."id"在left join之后为null的记录。
eager_load(:observations).where(
'"observations"."id" is null or "observations"."id" in (?)', Observation.pluck_from_each_station(limit)
)在两个条件下,它在逻辑上与left join相同,但是由于rails没有这个特性,所以可以使用where子句来绕过它。
https://stackoverflow.com/questions/40329799
复制相似问题