SQL 如何在 rails 中指定连接表的条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9033797/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to specify conditions on joined tables in rails
提问by espenhogbakk
I am trying to do a query in in Rails with ActiveRecord that specifies some condition on a joined table. And i can't seem to get it to work, even though i follow the examples from here:
我正在尝试使用 ActiveRecord 在 Rails 中进行查询，该查询在连接表上指定某些条件。而且我似乎无法让它工作，即使我遵循这里的例子：
http://guides.rubyonrails.org/active_record_querying.html#specifying-conditions-on-the-joined-tables
http://guides.rubyonrails.org/active_record_querying.html#specifying-conditions-on-the-joined-tables
From the guides:
从指南：
Client.joins(:orders).where(:orders => {:created_at => time_range})
Client.joins(:orders).where(:orders => {:created_at => time_range})
My database schema looks like this, with tables scores, submissionsand tasks:
我的数据库架构是这样的，有桌子scores，submissions和tasks：
create_table "scores", :force => true do |t|
t.integer "value"
t.integer "user_id"
t.datetime "created_at"
t.datetime "updated_at"
end
add_index "scores", ["user_id"], :name => "index_scores_on_user_id"
create_table "submissions", :force => true do |t|
t.integer "user_id"
t.integer "task_id"
t.integer "score_id"
t.datetime "completed_at"
t.datetime "created_at"
t.datetime "updated_at"
end
add_index "submissions", ["score_id"], :name => "index_submissions_on_score_id"
add_index "submissions", ["task_id"], :name => "index_submissions_on_task_id"
add_index "submissions", ["user_id"], :name => "index_submissions_on_user_id"
create_table "tasks", :force => true do |t|
t.integer "episode_id"
t.integer "score"
t.string "key"
t.datetime "created_at"
t.datetime "updated_at"
end
So i want to do a query where I can find all "scores" that have a relation to a spesific task. Submission belongs to tasks and scores.
所以我想做一个查询，在那里我可以找到与特定任务有关的所有“分数”。提交属于任务和分数。
My query now looks like this:
我的查询现在看起来像这样：
Score.joins(:submission).where(:submission => {:task_id => 1})
This generates the following syntax:
这会生成以下语法：
SELECT "scores".* FROM "scores" INNER JOIN "submissions" ON "submissions"."score_id" = "scores"."id" WHERE "submission"."task_id" = 1
Which generates the following error:
这会产生以下错误：
SQLite3::SQLException: no such column: submission.task_id
But there is a column submission.task_id, which you can see in the db schema. And i can do this successfully:
但是有一个 column submission.task_id，您可以在 db schema 中看到它。我可以成功地做到这一点：
SELECT "submissions".* FROM "submissions" WHERE "submissions"."task_id" = 1
回答by Nick
The name in the clause should be plural to reference the table name:
子句中的名称应为复数以引用表名：
Score.joins(:submission).where(:submissions => {:task_id => 1})
回答by leandrotk
The clause name should be plural to reference the table name.
子句名称应为复数以引用表名。
Score.joins(:submission).where(submissions: { task_id: 1 })
If score has many submissions, the joins symbol should also be plural to reference the relation between Score and Submission.
如果 score 有很多提交，joins 符号也应该是复数，以引用 Score 和 Submission 之间的关系。
Score.joins(:submissions).where(submissions: { task_id: 1 })
回答by thedanotto
I find this to be easier.
我觉得这更容易。
Score.joins(:submission).merge(Submission.where(task_id: 1))
回答by Matt Hucke
A caveat: if you're using non-standard table names the above will fail like so:
一个警告：如果你使用非标准的表名，上面会像这样失败：
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "submissions"
To fix this, put (joined-model-class).table_nameas a key in the wherehash:
要解决此问题，请将其(joined-model-class).table_name作为键放where入哈希中：
Score.joins(:submission).where(
Submission.table_name => {task_id: 1}
)
回答by jmjm
Your query looks like this:
您的查询如下所示：
Score.joins(:submission).where(:submission => { :task_id => 1 })
Your #joinsis correct, but :submissionshould be plural:
你#joins是对的，但:submission应该是复数：
Score.joins(:submission).where(:submissions => { :task_id => 1 })
