I'm upgrading a Rails 3 application to a Rails 7 application. A lot of things that worked in Rails 3 (Ruby 1.9.3) just aren't going to work in Rails 7 (Ruby 3.2.2). Though I'm confused on this behaviour from the join statement in conjunction with a where clause
When running this query:
Request.joins(job: :quote).to_sql
I get the response
"SELECT "requests".* FROM "requests"
INNER JOIN "jobs" ON "jobs"."id" = "requests"."job_id"
INNER JOIN "quotes" ON "quotes"."job_id" = "jobs"."id""
Which makes sense, a simple join. The confusing part comes when I attempt to use these associations inside of a where clause, like this:
Request.joins(job: :quote).where(job: {quote: {attachment_updated_at: nil}}).to_sql
which gives me:
"SELECT "requests".* FROM "requests"
INNER JOIN "jobs" "job" ON "job"."id" = "requests"."job_id"
INNER JOIN "quotes" ON "quotes"."job_id" = "job"."id"
WHERE "quote"."attachment_updated_at" IS NULL"
Trying to utilize the expected object result of this query with:
Request.joins(job: :quote).where(job: {quote: {attachment_updated_at: nil}}).pluck(:id)
gives me:
ERROR: missing FROM-clause entry for table "quote"
Which is expected because quote is not declared in the select statement. Why is the where clause changing the join statement? See the difference between JOIN "jobs" "job" in the two queries.
A simple fix to this would be to rename quote to quotes in the where clause. but that seems really hacky (I'm not looking for quotes, I'm looking for a singular quote) and it's almost like I'm just doing the whole nested query thing wrong. Should all association references be pluralized in the where clauses?
Rails uses database table names for where and association names for joins, includes and eager_load.
Request.joins(job: :quote).where(jobs: {quotes: {attachment_updated_at: nil}}).pluck(:id)
Another tip: do not forget to add distinct to avoid duplicate requests.
Request.joins(job: :quote).where(jobs: {quotes: {attachment_updated_at: nil}}).distinct.pluck(:id)