Rails ERROR: 'missing FROM-clause entry' when order through associated model-CodePudding

In my Rails 5 and Ruby 2.4 app I've got two models CaregiverAuthorization and 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

Now I want to have all pending caregiver_authorizations ordered by registrant.full_name. Pretty simple, so I did:

CaregiverAuthorization.includes(:registrant).order("registrants.full_name")

Surprisingly I got below error:

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "registrants"
LINE 1: ...id" AND "users"."type" IN ('Registrant') ORDER BY registrant...
                                                             ^

What did I missed?

[Edit]

Full error message:

  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

Maybe this will be helpfully:

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"

CodePudding user response：

Please note that includes does not necessarily join tables in the database query. It is built to avoid N 1 queries and it might happen that it simply runs two queries (one for the caregiver_authorizations and another for the associated registrants.

When you really need both tables in one query because you need to filter or order by them then you need to use joins. Additionally, the table is not named registrants but users because Registrant is a subclass of User. And you cannot order by full_name because full_name is not a database query but a method in your model. To order by full_name you need to simulate the behavior of that method in SQL.

I guess the following should work for you:

CaregiverAuthorization
  .pending
  .joins(:registrant)
  .order(Arel.sql("LOWER(CONCAT(users.first_name, ' ', users.last_name))"))