CaregiverAuthorization在我的 Rails 5 和 Ruby 2.4 應用程式中，我有兩個模型Registrant：
# caregiver_authorization
class CaregiverAuthorization < ApplicationRecord
belongs_to :registrant
scope :pending, (-> { where(status: [statuses[:pending], statuses[:pending_renewal]]) })
end
# registrant
class Registrant < User
has_many :caregiver_authorizations,
(-> { order('created_at asc') })
def full_name
[first_name, last_name].join(' ')
end
end
現在我想讓所有待處理caregiver_authorizations的訂單都由registrant.full_name. 很簡單，所以我做了：
CaregiverAuthorization.includes(:registrant).order("registrants.full_name")
令人驚訝的是，我得到了以下錯誤：
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "registrants"
LINE 1: ...id" AND "users"."type" IN ('Registrant') ORDER BY registrant...
^
我錯過了什么？
[編輯]
完整的錯誤資訊：
SQL (0.8ms) SELECT "caregiver_authorizations"."id" AS t0_r0, "caregiver_authorizations"."registrant_id" AS t0_r1, "caregiver_authorizations"."reviewer_id" AS t0_r2, "caregiver_authorizations"."reviewed_at" AS t0_r3, "caregiver_authorizations"."status" AS t0_r4, "caregiver_authorizations"."created_at" AS t0_r5, "caregiver_authorizations"."updated_at" AS t0_r6, "users"."id" AS t1_r0, "users"."external_system_id" AS t1_r1, "users"."external_system_type" AS t1_r2, "users"."type" AS t1_r3, "users"."login_id" AS t1_r4, "users"."first_name" AS t1_r5, "users"."last_name" AS t1_r6, "users"."status" AS t1_r7, "users"."minor" AS t1_r8, "users"."created_at" AS t1_r9, "users"."updated_at" AS t1_r10, "users"."paper_entry" AS t1_r11, "users"."date_of_birth" AS t1_r12, "users"."caregiver_limit_override" AS t1_r13, "users"."patient_limit_override" AS t1_r14 FROM "caregiver_authorizations" LEFT OUTER JOIN "users" ON "users"."id" = "caregiver_authorizations"."registrant_id" AND "users"."type" IN ('Registrant') ORDER BY registrants.full_name
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "registrants"
LINE 1: ...id" AND "users"."type" IN ('Registrant') ORDER BY registrant...
^
: SELECT "caregiver_authorizations"."id" AS t0_r0, "caregiver_authorizations"."registrant_id" AS t0_r1, "caregiver_authorizations"."reviewer_id" AS t0_r2, "caregiver_authorizations"."reviewed_at" AS t0_r3, "caregiver_authorizations"."status" AS t0_r4, "caregiver_authorizations"."created_at" AS t0_r5, "caregiver_authorizations"."updated_at" AS t0_r6, "users"."id" AS t1_r0, "users"."external_system_id" AS t1_r1, "users"."external_system_type" AS t1_r2, "users"."type" AS t1_r3, "users"."login_id" AS t1_r4, "users"."first_name" AS t1_r5, "users"."last_name" AS t1_r6, "users"."status" AS t1_r7, "users"."minor" AS t1_r8, "users"."created_at" AS t1_r9, "users"."updated_at" AS t1_r10, "users"."paper_entry" AS t1_r11, "users"."date_of_birth" AS t1_r12, "users"."caregiver_limit_override" AS t1_r13, "users"."patient_limit_override" AS t1_r14 FROM "caregiver_authorizations" LEFT OUTER JOIN "users" ON "users"."id" = "caregiver_authorizations"."registrant_id" AND "users"."type" IN ('Registrant') ORDER BY registrants.full_name
也許這會有所幫助：
2.4.5 :065 > CaregiverAuthorization.includes(:registrant).order("registrants.full_name").to_sql
=> "SELECT \"caregiver_authorizations\".\"id\" AS t0_r0, \"caregiver_authorizations\".\"registrant_id\" AS t0_r1, \"caregiver_authorizations\".\"reviewer_id\" AS t0_r2, \"caregiver_authorizations\".\"reviewed_at\" AS t0_r3, \"caregiver_authorizations\".\"status\" AS t0_r4, \"caregiver_authorizations\".\"created_at\" AS t0_r5, \"caregiver_authorizations\".\"updated_at\" AS t0_r6, \"users\".\"id\" AS t1_r0, \"users\".\"external_system_id\" AS t1_r1, \"users\".\"external_system_type\" AS t1_r2, \"users\".\"type\" AS t1_r3, \"users\".\"login_id\" AS t1_r4, \"users\".\"first_name\" AS t1_r5, \"users\".\"last_name\" AS t1_r6, \"users\".\"status\" AS t1_r7, \"users\".\"minor\" AS t1_r8, \"users\".\"created_at\" AS t1_r9, \"users\".\"updated_at\" AS t1_r10, \"users\".\"paper_entry\" AS t1_r11, \"users\".\"date_of_birth\" AS t1_r12, \"users\".\"caregiver_limit_override\" AS t1_r13, \"users\".\"patient_limit_override\" AS t1_r14 FROM \"caregiver_authorizations\" LEFT OUTER JOIN \"users\" ON \"users\".\"id\" = \"caregiver_authorizations\".\"registrant_id\" AND \"users\".\"type\" IN ('Registrant') ORDER BY registrants.full_name"
uj5u.com熱心網友回復：
請注意，includes在資料庫查詢中不一定要連接表。它是為了避免 N 1 查詢而構建的，它可能會簡單地運行兩個查詢（一個用于 the caregiver_authorizations，另一個用于關聯的registrants.
當您確實需要在一個查詢中使用兩個表時，因為您需要按它們過濾或排序，那么您需要使用joins. 此外，該表未命名registrants，但users因為Registrant是User. 而且您不能按順序排序，full_name因為full_name它不是資料庫查詢，而是模型中的一種方法。要訂購，full_name您需要在 SQL 中模擬該方法的行為。
我想以下內容應該適合您：
CaregiverAuthorization
.pending
.joins(:registrant)
.order(Arel.sql("LOWER(CONCAT(users.first_name, ' ', users.last_name))"))
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/426347.html