如何基于多对多关联生成多记录？Rails模型关联问题

我在Rails项目里有两个多对多关联的模型Physician和Patient，通过Appointment中间表关联。现在需要实现一个查询，让主模型记录数和关联数相乘——也就是同一个主模型对应每个关联生成一条独立记录。

我试过用Scenic创建ExtendedPhysician视图，还在模型里设置了has_one关联，但RSpec测试通不过：返回的Patient ID重复（比如[patient1.id, patient1.id]），预期应该是[patient1.id, patient2.id]。求正确实现方式，不局限于has_one关联，用模型方法也行。

相关代码 #

模型文件

# models/physician.rb
class Physician < ApplicationRecord
  has_many :appointments
  has_many :patients, through: :appointments
end

# models/patient.rb
class Patient < ApplicationRecord
  has_many :appointments
  has_many :physicians, through: :appointments
end

# models/appointment.rb
class Appointment < ApplicationRecord
  belongs_to :physician
  belongs_to :patient
end

# models/extended_physician.rb
class ExtendedPhysician < ApplicationRecord
  self.primary_key = 'id'

  has_one :appointment, foreign_key: 'physician_id'
  has_one :patient, foreign_key: 'physician_id', through: :appointment
end

Scenic视图定义

# 初始视图SQL
SELECT "physicians".* FROM "physicians"
LEFT OUTER JOIN "appointments" ON "appointments"."physician_id" = "physicians"."id"
LEFT OUTER JOIN "patients" ON "patients"."id" = "appointments"."patient_id"

# schema中的视图定义
create_view "extended_physicians", sql_definition: <<-SQL
    SELECT physicians.id,
    physicians.name,
    physicians.created_at,
    physicians.updated_at
   FROM ((physicians
     LEFT JOIN appointments ON ((appointments.physician_id = physicians.id)))
     LEFT JOIN patients ON ((patients.id = appointments.patient_id)));
SQL

数据库Schema

create_table "appointments", force: :cascade do |t|
  t.bigint "physician_id"
  t.bigint "patient_id"
  t.datetime "appointment_date"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.index ["patient_id"], name: "index_appointments_on_patient_id"
  t.index ["physician_id"], name: "index_appointments_on_physician_id"
end

create_table "patients", force: :cascade do |t|
  t.string "name"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

create_table "physicians", force: :cascade do |t|
  t.string "name"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

RSpec测试

RSpec.describe ExtendedPhysician, type: :model do
  describe 'patiens relation' do
    it 'retunrs correct patiens' do
      patient1 = Patient.create!
      patient2 = Patient.create!

      Physician.create!(patients: [patient1, patient2])

      expect(ExtendedPhysician.count).to eq(2)

      # 测试不通过，返回的Patient ID重复
      expect(ExtendedPhysician.all.map(&:patient).pluck(:id)).to eq([patient1.id, patient2.id])
    end
  end
end

问题分析 #

1. 视图字段缺失：初始视图只返回physicians表的字段，没有携带appointments或patients的唯一标识，导致Rails无法区分同个医生对应的不同患者记录，关联查询时只会匹配到第一条关联数据。
2. 关联逻辑错误：ExtendedPhysician中使用has_one :appointment不符合实际关联关系（一个医生对应多个预约），视图本身已经是展开后的结果，不需要再通过has_one关联反向查询。

解决方案 #

方案一：修正Scenic视图与模型关联 #

1. 更新视图定义

在视图中加入患者、预约的唯一标识字段，让每条记录对应唯一的医生-患者组合：

create_view "extended_physicians", sql_definition: <<-SQL
    SELECT 
      physicians.id AS physician_id,
      physicians.name AS physician_name,
      physicians.created_at AS physician_created_at,
      physicians.updated_at AS physician_updated_at,
      patients.id AS patient_id,
      appointments.id AS appointment_id
    FROM physicians
    LEFT JOIN appointments ON appointments.physician_id = physicians.id
    LEFT JOIN patients ON patients.id = appointments.patient_id;
SQL

2. 调整ExtendedPhysician模型

直接通过视图中已有的字段建立关联，或直接读取字段：

class ExtendedPhysician < ApplicationRecord
  self.table_name = 'extended_physicians'
  # 若不需要完整关联，可直接读取patient_id字段，无需定义belongs_to
  belongs_to :patient, optional: true, foreign_key: 'patient_id'
  belongs_to :physician, foreign_key: 'physician_id'
end

3. 修正测试用例

RSpec.describe ExtendedPhysician, type: :model do
  describe 'patient association' do
    it 'returns correct patients' do
      patient1 = Patient.create!
      patient2 = Patient.create!
      Physician.create!(patients: [patient1, patient2])

      expect(ExtendedPhysician.count).to eq(2)
      expect(ExtendedPhysician.pluck(:patient_id)).to match_array([patient1.id, patient2.id])
      expect(ExtendedPhysician.all.map(&:patient)).to include(patient1, patient2)
    end
  end
end

方案二：无需视图，直接用模型方法生成展开记录 #

如果不想依赖Scenic视图，可直接在Physician模型中定义方法生成展开后的记录：

class Physician < ApplicationRecord
  has_many :appointments
  has_many :patients, through: :appointments

  # 返回每个医生对应每个患者的独立记录
  def self.with_flattened_patients
    joins(:appointments, :patients).select(
      'physicians.*',
      'patients.id AS patient_id',
      'patients.name AS patient_name'
    )
  end
end

对应的测试用例：

RSpec.describe Physician, type: :model do
  describe '#with_flattened_patients' do
    it 'returns flattened physician-patient records' do
      patient1 = Patient.create!
      patient2 = Patient.create!
      Physician.create!(patients: [patient1, patient2])

      result = Physician.with_flattened_patients
      expect(result.count).to eq(2)
      expect(result.pluck(:patient_id)).to match_array([patient1.id, patient2.id])
    end
  end
end

内容的提问来源于stack exchange，提问作者oczek.scala