SQL ActiveRecord 加入查询并在 Rails 中选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22312901/
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
ActiveRecord Join Query and select in Rails
提问by Bharat
In my rails 4 application, a client (clients table) can have many projects (projects table). I have a column called namein each table. I am trying to write a joinand then selectwhich uses projects as the base table and clients as the lookup table. client_idis the foreign_keyin the projects table:
在我的 rails 4 应用程序中，一个客户端（clients 表）可以有多个项目（projects 表）。我name在每个表中都有一个列。我正在尝试编写一个join然后select使用项目作为基表和客户端作为查找表。client_id是foreign_key在项目表中：
I am writing my query as follows:
我正在编写我的查询如下：
Project.joins(:client).select('projects.id,projects.name,clients.name')
I get the following response:
我收到以下回复：
Project Load (0.6ms) SELECT projects.id,projects.name,clients.name FROM "projects" INNER JOIN "clients" ON "clients"."id" = "projects"."client_id"
=> #<ActiveRecord::Relation [#<Project id: 1, name: "Fantastico Client">]>
If I try to alias it like so:
如果我尝试这样别名：
Project.joins(:client).select('projects.id,projects.name,clients.name as client_name')
Then I get the following response:
然后我得到以下回复：
Project Load (0.8ms) SELECT projects.id,projects.name,clients.name as client_name FROM "projects" INNER JOIN "clients" ON "clients"."id" = "projects"."client_id"
=> #<ActiveRecord::Relation [#<Project id: 1, name: "The Dream Project">]>
In either case, ActiveRecord looses one of the names as you can see from the above response. How should I be writing this query?
无论哪种情况，ActiveRecord 都会丢失其中一个名称，正如您从上面的响应中看到的那样。我应该如何编写此查询？
回答by vee
If the column in selectis not one of the attributes of the model on which the selectis called on then those columns are not displayed. All of these attributes are still contained in the objects within AR::Relationand are accessible as any other public instance attributes.
如果列 inselect不是select调用的模型的属性之一，则不会显示这些列。所有这些属性仍然包含在对象中AR::Relation，并且可以像任何其他公共实例属性一样访问。
You could verify this by calling first.client_name:
您可以通过调用来验证这一点first.client_name：
Project.joins(:client)
.select('projects.id,projects.name,clients.name as client_name')
.first.client_name
回答by Ryan Taylor
You can use :'clients.name'as one of your symbols. For instance:
您可以:'clients.name'用作您的符号之一。例如：
Project.select(:id, :name, :'clients.name').joins(:client)
I like it better because it seems like Rails understands it, since it quotes all parameters:
我更喜欢它，因为 Rails 似乎理解它，因为它引用了所有参数：
SELECT "projects"."id", "projects"."name", "clients"."name"
FROM "projects"
INNER JOIN "clients" ON "clients"."id" = "projects"."client_id"
(I'm not 100% sure that's the exact SQL query, but I'm fairly certain and I promise it willuse "clients"."name")
（我不是 100% 确定那是确切的 SQL 查询，但我相当确定并且我保证它会使用"clients"."name"）
回答by uma
your query don't looses any thing. Actually you have applied join on models and you have written Project.joins(:client) that why it is looking like. means It will hold Project related data as it is and associated data hold with alias name that you have given 'client_name' in your query.
您的查询不会丢失任何东西。实际上，您已经在模型上应用了连接，并且您编写了 Project.joins(:client) 这就是为什么它看起来像。意味着它将按原样保存与项目相关的数据，并使用您在查询中指定的“client_name”别名保存相关数据。
if you use
如果你使用
Project.joins(:client)
.select('projects.id project_id, projects.name projects_name,clients.name as client_name')
then it look like [#, #]
那么它看起来像 [#, #]
but it hold all the attribute that you selected.
但它包含您选择的所有属性。
回答by hari
To get both project table name and client name you can do like below query
要同时获取项目表名称和客户端名称，您可以执行以下查询
Project.joins(:client).pluck(:name,:'clients.name')
Project.joins(:client).pluck(:name,:'clients.name')
