Rails Rails 4 中的 through 与 scope 共存时的 includes 查询,这个坑如何跳

davidwei · 2016年02月23日 · 最后由 davidwei 回复于 2016年02月25日 · 2588 次阅读

题目有点拗口。请忽略。

下面是正题:

现在我有 3 个 model,分别是 Pyysician、Appointment、Patient;他们的关系如下:

class Physician < ActiveRecord::Base
  has_many :appointments
  has_many :patients,  through: :appointments
 has_many :healed_patients, -> { where(state: Patient::HEALED).order('appointments.created_at DESC')},  through: :appointments, class_name: 'Patient'
end

class Appointment < ActiveRecord::Base
  belongs_to :physician
  belongs_to :patient
  belongs_to :healed_patient, class_name: 'Patient',  foreign_key: 'patient_id'
end

class Patient < ActiveRecord::Base
  HEALED = 1

  has_many :appointments
  has_many :physicians, through: :appointments
end

如果现在有 Physician 的一个实例physician,用它调用physician.healed_patients是可以正常工作的。如果使用Physician.includes(:healed_patients).last,则会报错。

错误如下:

2.2.1 :001 > phy = Physician.last
  Physician Load (0.1ms)  SELECT  "physicians".* FROM "physicians"  ORDER BY "physicians"."id" DESC LIMIT 1
 => #<Physician id: 1, created_at: "2016-02-23 14:40:38", updated_at: "2016-02-23 14:40:38">
2.2.1 :002 > phy.healed_patients
  Patient Load (0.2ms)  SELECT "patients".* FROM "patients" INNER JOIN "appointments" ON "patients"."id" = "appointments"."patient_id" WHERE "appointments"."physician_id" = ? AND "patients"."state" = ?  ORDER BY appointments.created_at DESC  [["physician_id", 1], ["state", 1]]
 => #<ActiveRecord::Associations::CollectionProxy [#<Patient id: 1, state: 1, created_at: "2016-02-23 14:40:56", updated_at: "2016-02-23 14:40:56">]>
2.2.1 :003 > phy = Physician.includes(:healed_patients).last
  Physician Load (0.2ms)  SELECT  "physicians".* FROM "physicians"  ORDER BY "physicians"."id" DESC LIMIT 1
  Appointment Load (0.7ms)  SELECT "appointments".* FROM "appointments" WHERE "patients"."state" = ? AND "appointments"."physician_id" IN (1)  [["state", 1]]
ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: patients.state: SELECT "appointments".* FROM "appointments" WHERE "patients"."state" = ? AND "appointments"."physician_id" IN (1)

请问有什么方法解决 includes 的问题否 或者 我的这个写法哪里有问题? 谢谢诸位。谢谢

试一下 这样

Physician.includes(:appointments, :healed_patients).last

报错说:no such column: patients.state`, 看看你的 db/schema.rb 里面,patients 表里面有 state 这个字段吗?是不是有 migration 没有运行?

#2 楼 @leomayleomay state 在 Physician 表上,如果没法使用 includes 实现,就手写 sql 语句实现,避免 N+1 问题也是可以的,只是难看了点

对比第二条和最后一条查询,你会发现第二条有 Join 操作,而最后一条是没有的,问题出在这里FROM "appointments" WHERE "patients"."state" = ?

另外从代码语义上来说,感觉Physician.includes(:healed_patients).last 意图不明确,最后一位康复的病人?,还是有康复病人的最后一位医生?

详见https://ruby-china.org/topics/28251,希望可以帮到你。

#1 楼 @pathbox It doesn't work. :-)

#4 楼 @qinfanpeng

  • Physician.includes(:healed_patients).last仅作问题测试之用,真实场景用 where 或 find 的。
  • Thanks,链接我看下。

那个 block 似乎应该放到 patient 里面

class Physician < ActiveRecord::Base
  has_many :appointments
  has_many :patients,  through: :appointments
 has_many :healed_patients,  through: :healed_appointments, class_name: 'Appointment'
end

class Appointment < ActiveRecord::Base
  belongs_to :physician
  belongs_to :patient
  belongs_to :healed_patient, class_name: 'Patient',  foreign_key: 'patient_id'
end

class Patient < ActiveRecord::Base
  HEALED = 1

  has_many :healed_appointments, -> { where(state: Patient::HEALED).order('appointments.created_at DESC')}, class_name: 'Appointment'
  has_many :appointments
  has_many :physicians, through: :appointments
end

看起来像是 ActiveRecord 的 bug

Physician.where_has_healed_patient.include(:patients).last # 找到有治愈病人的最后一个医生

其实语义好像是这个的样子 我理解 include 只是用来拿“一对多”的“多”的,你的写法没有体现“找到有治愈病人的医生”这个意思,直接读是“我要所有医生中的最后一个,带上他的治愈病人的信息”。

不是很清楚 rails 这里具体怎么实现,只是从语义上看。

或者你就是是要找第一个医生,并带上他的治愈病人信息?

#7 楼 @lithium4010 谢谢。

语义可以理解为:找到最后一个医生,和他治疗好的病人

您回复的内容是不是有问题? where_has_healed_patient这个方法在Physician上不存在。

另外:

class Physician < ActiveRecord::Base
  has_many :appointments
  has_many :patients,  through: :appointments
 has_many :healed_patients,  through: :healed_appointments, class_name: 'Appointment'
end

最后一句是不是写错了?

需要 登录 后方可回复, 如果你还没有账号请 注册新账号