Rails错误:通过关联模型订购时“从-子句项中丢失”
Rails错误:通过关联模型订购时“从-子句项中丢失”
提问于 2022-02-16 05:19:26
在我的Rails 5和Ruby2.4应用程序中，我有两个模型：CaregiverAuthorization和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"回答 1
Stack Overflow用户
回答已采纳
发布于 2022-02-16 05:32:36
请注意，includes不一定要连接数据库查询中的表。它是为避免N+1查询而构建的，它可能只运行两个查询(一个用于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))"))页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71142614
复制相关文章
相似问题
JQuery\Javascript -将函数作为变量传递
将jQuery ID作为参数传递给JavaScript函数
jquery noUiSlider将外部javascript变量传递到函数中。
将变量作为对javascript/jquery函数的引用传递
将字段id作为javascript函数的变量传递时出错
添加站长 进交流群
领取专属 10元无门槛券
AI混元助手 在线答疑
关注 腾讯云开发者公众号
洞察 腾讯核心技术
剖析业界实践案例
腾讯云开发者
