[Rails 5.1]サブクエリのLEFT JOIN 実現できない？？ 【解決】

やりたいこと

• 患者を検索する（Ransack）
• 患者の未来の予約状況も同時に取得したい
• 出来ればレスポンスてきに１SQLが良い
• 以下のダイアログの予約状況を表示したい（ajaxでjson形式でデータ取得）

試した方法１

未来の予約状況を取得するコードを書いて、そのSQLをjoinさせる。
以下のように

reserved_table = ReservedTable.where('reservation_date >= ?', Date.current)
@patients = Patient
 .joins("LEFT OUTER JOIN (#{reserved_table.to_sql}) AS reserved_tables 
         ON patients.id = reserved_tables.patient_id")
 .ransack(:patient_name_or_patient_name_kana_or_patient_card_number_or_phone_number_or_mobile_phone_number_cont => params[:q])
 .result.select('patients.*, reserved_tables.*')

 p @patients

SQLは、想定どおりになったのですが、予約がない患者の結果がpatientのidがNilになってしまうのと
予約テーブルの結果が取れてない。

SELECT  patients.*, reserved_tables.* 
FROM "patients" 
LEFT OUTER JOIN (
  SELECT "reserved_tables".* 
  FROM "reserved_tables" 
  WHERE (reservation_date >= '2020-01-02')
) AS reserved_tables 
ON patients.id = reserved_tables.patient_id 
WHERE (((("patients"."patient_name" ILIKE '%タロウ%' OR "patients"."patient_name_kana" ILIKE '%タロウ%') 
OR "patients"."patient_card_number" ILIKE '%タロウ%') 
OR "patients"."phone_number" ILIKE '%タロウ%') 
OR "patients"."mobile_phone_number" ILIKE '%タロウ%') 

結果
左のテーブルのidがnilになってしまうのがわからないのと、予約の結果が取れないので違う方法を検討

<ActiveRecord::Relation [
<Patient id: nil, patient_name: "試験太郎", patient_name_kana: "シケンタロウ", patient_card_number: "0003", birth_date: "1989-10-05", gender: 1, phone_number: "0312345678", mobile_phone_number: "09012345678", email_address: "[e-mail]", remark: "", status: nil, clinic_id: nil, created_at: nil, updated_at: nil>, 
<Patient id: "23d4f957-584b-4737-a01e-057af6252cdc", patient_name: "試験太郎", patient_name_kana: "シケンタロウ", patient_card_number: "0004", birth_date: "1989-10-05", gender: 1, phone_number: "0312345678", mobile_phone_number: "09012345678", email_address: "[e-mail]", remark: "", status: nil, clinic_id: "e525f6c3-cf9a-4d50-a982-d09679cc555d", created_at: "2020-01-02 06:43:47", updated_at: "2020-01-02 06:43:47">, 
<Patient id: nil, patient_name: "試験太郎", patient_name_kana: "シケンタロウ", patient_card_number: "0004", birth_date: "1989-10-05", gender: 1, phone_number: "0312345678", mobile_phone_number: "09012345678", email_address: "[e-mail]", remark: "", status: nil, clinic_id: nil, created_at: nil, updated_at: nil>, 
<Patient id: nil, patient_name: "テスト太郎", patient_name_kana: "テストタロウ", patient_card_number: "1234", birth_date: "1976-09-27", gender: 1, phone_number: "0312345678", mobile_phone_number: "09012345678", email_address: "[e-mail]", remark: "", status: 1, clinic_id: nil, created_at: nil, updated_at: nil>, 
<Patient id: nil, patient_name: "マンハッタン太郎", patient_name_kana: "マンハッタンタロウ", patient_card_number: "8888", birth_date: "1980-07-15", gender: 1, phone_number: "0312345678", mobile_phone_number: "09012345678", email_address: "[e-mail]", remark: "", status: nil, clinic_id: nil, created_at: nil, updated_at: nil>
]>

＜2020/01/03 追記＞
idがnilになってしまう理由がわかりました。
joinするテーブルのidとカラム名が被ってしまうためでした。

方法１で継続調査中。

データは正しく取得できました。

@patients.each do |patient|
  p patient.attributes
end

#render :json => @patients
render :json => @patients.map{ |patient| patient.attributes }

結果
2行目のデータにreserved_idがセットされる。
この状態をJSONで返せればOKっぽい。
renderの書き方で良い方法ないかなぁ
→mapメソッド を使うことで解決しました！

{"id"=>"cbeaf96f-8312-4e48-9e84-c60e7c2f7361", "patient_name"=>"試験太郎", "patient_name_kana"=>"シケンタロウ", "patient_card_number"=>"0003", "birth_date"=>Thu, 05 Oct 1989, "gender"=>1, "phone_number"=>"0312345678", "mobile_phone_number"=>"09012345678", "email_address"=>"[e-mail]", "remark"=>"", "status"=>nil, "clinic_id"=>nil, "created_at"=>Sat, 05 Oct 2019 17:52:05 JST +09:00, "updated_at"=>Sat, 05 Oct 2019 17:52:05 JST +09:00, "reserved_id"=>nil, "reservation_date"=>nil, "reservation_start_time"=>nil, "reservation_end_time"=>nil}
{"id"=>"a1dc4735-8f53-47e4-a3ed-15b2880e586e", "patient_name"=>"試験太郎", "patient_name_kana"=>"シケンタロウ", "patient_card_number"=>"0004", "birth_date"=>Thu, 05 Oct 1989, "gender"=>1, "phone_number"=>"0312345678", "mobile_phone_number"=>"09012345678", "email_address"=>"[e-mail]", "remark"=>"", "status"=>nil, "clinic_id"=>nil, "created_at"=>Sat, 05 Oct 2019 17:56:16 JST +09:00, "updated_at"=>Sat, 05 Oct 2019 17:56:16 JST +09:00, "reserved_id"=>"23d4f957-584b-4737-a01e-057af6252cdc", "reservation_date"=>Fri, 03 Jan 2020, "reservation_start_time"=>2000-01-01 00:30:00 UTC, "reservation_end_time"=>2000-01-01 01:15:00 UTC}
{"id"=>"d06ac829-792a-4b8a-923c-83194736ca3c", "patient_name"=>"試験太郎", "patient_name_kana"=>"シケンタロウ", "patient_card_number"=>"0004", "birth_date"=>Thu, 05 Oct 1989, "gender"=>1, "phone_number"=>"0312345678", "mobile_phone_number"=>"09012345678", "email_address"=>"[e-mail]", "remark"=>"", "status"=>nil, "clinic_id"=>nil, "created_at"=>Sat, 05 Oct 2019 17:59:48 JST +09:00, "updated_at"=>Sat, 05 Oct 2019 17:59:48 JST +09:00, "reserved_id"=>nil, "reservation_date"=>nil, "reservation_start_time"=>nil, "reservation_end_time"=>nil}
{"id"=>"c0e33986-3784-4138-aced-861ef8e32c7d", "patient_name"=>"テスト太郎", "patient_name_kana"=>"テストタロウ", "patient_card_number"=>"1234", "birth_date"=>Mon, 27 Sep 1976, "gender"=>1, "phone_number"=>"0312345678", "mobile_phone_number"=>"09012345678", "email_address"=>"[e-mail]", "remark"=>"", "status"=>1, "clinic_id"=>"e525f6c3-cf9a-4d50-a982-d09679cc555d", "created_at"=>Fri, 27 Sep 2019 07:07:30 JST +09:00, "updated_at"=>Mon, 18 Nov 2019 06:43:08 JST +09:00, "reserved_id"=>nil, "reservation_date"=>nil, "reservation_start_time"=>nil, "reservation_end_time"=>nil}
{"id"=>"2bd0c123-fe58-4e85-85e3-5ed1155333b8", "patient_name"=>"マンハッタン太郎", "patient_name_kana"=>"マンハッタンタロウ", "patient_card_number"=>"8888", "birth_date"=>Tue, 15 Jul 1980, "gender"=>1, "phone_number"=>"0312345678", "mobile_phone_number"=>"09012345678", "email_address"=>"[e-mail]", "remark"=>"", "status"=>nil, "clinic_id"=>"e525f6c3-cf9a-4d50-a982-d09679cc555d", "created_at"=>Mon, 18 Nov 2019 06:56:46 JST +09:00, "updated_at"=>Mon, 18 Nov 2019 06:57:00 JST +09:00, "reserved_id"=>nil, "reservation_date"=>nil, "reservation_start_time"=>nil, "reservation_end_time"=>nil}

試した方法２

eager_loadでサブクエリをしてみようと試す。
結論から言うと、エラーが出てうまく行っていない。
困った。。。

patient = Patient.ransack(:patient_name_or_patient_name_kana_or_patient_card_number_or_phone_number_or_mobile_phone_number_cont => params[:q]).result
@patients = patient.eager_load(ReservedTable.where('reservation_date >= ?', Date.current))

結果

ActiveRecord::ConfigurationError (#<ActiveRecord::Relation [#<ReservedTable id: "23d4f957-584b-4737-a01e-057af6252cdc", reservation_date: "2020-01-03", reservation_start_time: "2000-01-01 00:30:00", reservation_end_time: "2000-01-01 01:15:00", first_visit_flag: nil, tentative_reservation_flag: nil, patient_id: "a1dc4735-8f53-47e4-a3ed-15b2880e586e", reserved_column_id: "ae2c8450-c406-4041-8751-97aea5008860", clinic_id: "e525f6c3-cf9a-4d50-a982-d09679cc555d", created_at: "2020-01-02 06:43:47", updated_at: "2020-01-02 06:43:47", reserved_content_id: nil, medical_content_id: "9b0b7904-de4e-4f47-b7c4-bfb190396738", remarks: nil>]>):

エラーとなってしまい、お手上げ状態です。。

どちらの方法でも良いのですが、LEFT OUTER JOINで患者と未来の予約状況を取りたいです。