英文:
Should all association references be pluralized in the where clauses?
问题
Here's the translated content:
"我正在将一个 Rails 3 应用升级到 Rails 7 应用。很多在 Rails 3 (Ruby 1.9.3) 中工作的东西在 Rails 7 (Ruby 3.2.2) 中不再适用。不过，我对 join 语句与 where 子句结合使用的行为感到困惑。
当运行以下查询时：
Request.joins(job: :quote).to_sql
我得到以下响应：
SELECT "requests".* FROM "requests"
INNER JOIN "jobs" ON "jobs"."id" = "requests"."job_id"
INNER JOIN "quotes" ON "quotes"."job_id" = "jobs"."id"
这是合理的，一个简单的连接操作。令人困惑的部分是当我尝试在 where 子句中使用这些关联时，像这样：
Request.joins(job: :quote).where(job: {quote: {attachment_updated_at: nil}}).to_sql
这给了我：
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
尝试使用此查询的预期对象结果：
Request.joins(job: :quote).where(job: {quote: {attachment_updated_at: nil}}).pluck(:id)
给了我：
ERROR: missing FROM-clause entry for table "quote"
这是预期的，因为选择语句中没有声明 "quote"。为什么 where 子句会改变 join 语句？ 请看两个查询中 "JOIN "jobs" "job"" 的不同之处。
修复
对此的一个简单修复是在 where 子句中将 "quote" 重命名为 "quotes"。但这似乎非常不正规（我不是在寻找引用，我是在寻找一个单独的引用），而且这几乎就像是我在整个嵌套查询中做错了事情。在 where 子句中，所有关联引用都应该使用复数形式吗？"
英文:
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.
Fixes
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?
答案1
得分: 3
Rails 使用数据库表名来指定 where，而使用关联名来指定 joins、includes 和 eager_load。
Request.joins(job: :quote).where(jobs: {quotes: {attachment_updated_at: nil}}).pluck(:id)
另一个提示：别忘了添加 distinct 来避免 重复 请求。
Request.joins(job: :quote).where(jobs: {quotes: {attachment_updated_at: nil}}).distinct.pluck(:id)
英文:
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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论