Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@hatopato

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

Last updated at Posted at 2020-01-02

#やりたいこと

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

##試した方法１
未来の予約状況を取得するコードを書いて、その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: "test@example.com", 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: "test@example.com", 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: "test@example.com", 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: "test@example.com", 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: "man@hattan.com", 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"=>"test@example.com", "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"=>"test@example.com", "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"=>"test@example.com", "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"=>"test@example.com", "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"=>"man@hattan.com", "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で患者と未来の予約状況を取りたいです。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?