Ruby-on-rails Rails 连接并包含连接表中的列

Rails Joins and include columns from joins table

提问by sethvargo

I don't understand how to get the columns I want from rails. I have two models - A User and a Profile. A User :has_many Profile (because users can revert back to an earlier version of their profile):

我不明白如何从 rails 获取我想要的列。我有两个模型 - 一个用户和一个配置文件。用户 :has_many 配置文件（因为用户可以恢复到其配置文件的早期版本）：

> DESCRIBE users;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| username       | varchar(255) | NO   | UNI | NULL    |                |
| password       | varchar(255) | NO   |     | NULL    |                |
| last_login     | datetime     | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

 

 

> DESCRIBE profiles;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id        | int(11)      | NO   | MUL | NULL    |                |
| first_name     | varchar(255) | NO   |     | NULL    |                |
| last_name      | varchar(255) | NO   |     | NULL    |                |
|      .                .          .      .       .             .       |
|      .                .          .      .       .             .       |
|      .                .          .      .       .             .       |
+----------------+--------------+------+-----+---------+----------------+

In SQL, I can run the query:

在 SQL 中，我可以运行查询：

> SELECT * FROM profiles JOIN users ON profiles.user_id = users.id LIMIT 1;
+----+-----------+----------+---------------------+---------+---------------+-----+
| id | username  | password | last_login          | user_id | first_name    | ... |
+----+-----------+----------+---------------------+---------+---------------+-----+
| 1  | john      | ******   | 2010-12-30 18:04:28 | 1       | John          | ... |
+----+-----------+----------+---------------------+---------+---------------+-----+

See how I get all the columns for BOTH tables JOINED together? However, when I run this same query in Rails, I don't get all the columns I want - I only get those from Profile:

看看我如何将 BOTH 表的所有列连接在一起？但是，当我在 Rails 中运行这个相同的查询时，我没有得到我想要的所有列 - 我只从 Profile 中得到那些：

# in rails console
>> p = Profile.joins(:user).limit(1)
>> [#<Profile ...>]
>> p.first_name
>> NoMethodError: undefined method `first_name' for #<ActiveRecord::Relation:0x102b521d0> from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.1/lib/active_record/relation.rb:373:in `method_missing' from (irb):8
# I do NOT want to do this (AKA I do NOT want to use "includes")
>> p.user
>> NoMethodError: undefined method `user' for #<ActiveRecord::Relation:0x102b521d0> from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.1/lib/active_record/relation.rb:373:in method_missing' from (irb):9

I want to (efficiently) return an object that has all the properties of Profile and User together. I don't want to :include the user because it doesn't make sense. The user should always be part of the most recent profile as if they were fields within the Profile model. How do I accomplish this?

我想（有效地）返回一个具有 Profile 和 User 的所有属性的对象。我不想 :include 用户，因为它没有意义。用户应该始终是最新配置文件的一部分，就好像它们是配置文件模型中的字段一样。我该如何实现？

I think the problem has something to do with the fact that the Profile model doesn't have attributes for User...

我认为这个问题与 Profile 模型没有 User 属性有关。

采纳答案by klew

I don't think that you can load users and profiles with join in Rails. I think that in earlier versions of Rails ( < 2.1) loading of associated models was done with joins, but it was not efficient. Hereyou have some explanation and links to other materials.

我不认为您可以通过加入 Rails 加载用户和配置文件。我认为在早期版本的 Rails ( < 2.1) 中，关联模型的加载是通过连接完成的，但效率不高。这里有一些解释和其他材料的链接。

So even if you explicite say that you want to join it, Rails won't map it to associated models. So if you say Profile.whatever_hereit will always be mapped to Profileobject.

因此，即使您明确表示要加入它，Rails 也不会将其映射到关联模型。因此，如果您说Profile.whatever_here它将始终映射到Profile对象。

If you still want to do what you said in question, then you can call custom sql query and process results by yourself:

如果你还想按照你说的做，那么你可以自己调用自定义sql查询并处理结果：

p = ActiveRecord::Base.connection.execute("SELECT * FROM profiles JOIN users ON profiles.user_id = users.id LIMIT 1")

and get results row by row with:

并逐行获取结果：

p.fetch_row

It will already be mappet to an array.

它已经是一个数组的映射。

Your errors are because you are calling first_nameand usermethod on AciveRecord::Relationobject and it stores an array of Profileobjects, not a single object. So

你的错误是因为你在对象上调用first_name和user方法AciveRecord::Relation，它存储一个Profile对象数组，而不是单个对象。所以

p = Profile.joins(:user).limit(1)
p[0].first_name

shoud work.

应该工作。

Better way to fetch only one record is to call:

仅获取一条记录的更好方法是调用：

p = Profile.joins(:user).first
p.first_name
p.user

But when you call p.userit will query database. To avoid it, you can use include, but if you load only one profile object, it is useless. It will make a difference if you load many profiles at a time and want to inlcude users table.

但是当你调用p.user它时会查询数据库。为了避免这种情况，您可以使用include，但如果您只加载一个配置文件对象，则它是无用的。如果您一次加载许多配置文件并希望包含用户表，这将会有所不同。

回答by Lex Lindsey

Use select() to name the columns you want. At least this works in Rails 3.0.9.

使用 select() 命名您想要的列。至少这适用于 Rails 3.0.9。

Background: my application has a primary table named :rights. I wanted to be able to ascribe a tag and color to a given :right record so I could easily pick it out of an index listing. This doesn't cleanly fit the Rails picture of associated records; most :rights will never be tagged, and the tags are completely arbitrary (user input via tag/edit).

背景：我的应用程序有一个名为 :rights 的主表。我希望能够将标签和颜色归于给定的 :right 记录，以便我可以轻松地从索引列表中挑选出来。这并不完全符合相关记录的 Rails 图片；大多数 :rights 永远不会被标记，并且标记是完全任意的（用户通过标记/编辑输入）。

I could try duplicating the tag data in the :right record, but that violates normal form. Or I could try querying :tags for each :right record, but that is a painfully inefficient approach. I want to be able to join the tables.

我可以尝试在 :right 记录中复制标签数据，但这违反了正常形式。或者我可以尝试为每个 :right 记录查询 :tags ，但这是一种非常低效的方法。我希望能够加入表格。

MySQL console shows:

MySQL控制台显示：

mysql> describe rights;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int(11)       | NO   | PRI | NULL    | auto_increment |

  ...

| Tagid      | int(11)       | YES  |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

mysql> describe tags;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| TagName    | varchar(255) | YES  |     | NULL    |                |
| TagColor   | varchar(255) | YES  |     | NULL    |                |
| created_at | datetime     | YES  |     | NULL    |                |
| updated_at | datetime     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

I am going to use TagName and TagColor in views/rights/index.html.erb, so I want the rights controller to include those columns in the @rights object it passes to the view. Since not every :right has a :tag, I want to use an outer join:

我将在views/rights/index.html.erb 中使用TagName 和TagColor，所以我希望权限控制器将这些列包含在它传递给视图的@rights 对象中。由于不是每个 :right 都有一个 :tag，我想使用外连接：

@rights = Right.joins("LEFT OUTER JOIN tags ON rights.Tagid = tags.id")

But, as everyone has found, this alone doesn't work: a block reference to TagName produces a server error. However, if I add a select at the end, all is well:

但是，正如每个人都发现的那样，仅此一项是行不通的：对 TagName 的块引用会产生服务器错误。但是，如果我在最后添加一个选择，一切都很好：

@rights = Right.joins("LEFT OUTER JOIN tags ON rights.Tagid = tags.id").select("rights.*,tags.TagName as TagName,tags.TagColor as TagColor")

Note added 6/7/13: the select clause does not require aliases - this works too:

13 年 7 月 6 日添加的注释：select 子句不需要别名 - 这也适用：

.select("rights.*,tags.TagName,tags.TagColor")

Now I can reference TagName and TagColor in my view:

现在我可以在我的视图中引用 TagName 和 TagColor：

<% @rights.each do |right| %>
  <tr ALIGN=Left <%=
  # color background if this is tagged
  " BGCOLOR=#{right.TagColor}" if right.TagColor
  %> > ...
<% end %>

回答by snoopy0123

Try using select("*").joins(:table)

尝试使用 select("*").joins(:table)

In this case, you would type:

在这种情况下，您将键入：

User.select("*").joins(:profile)

Hope that works for you.

希望这对你有用。

回答by David Silva Smith

After reading these tips I got the joins to all be loaded in one query by reading 3 ways to do eager loading (preloading) in Rails 3 & 4.

阅读这些技巧后，我通过阅读3 种在 Rails 3 & 4 中进行预先加载（预加载）的方法，将所有连接加载到一个查询中。

I'm using Rails 4 and this worked like a charm for me:

我正在使用 Rails 4，这对我来说很有魅力：

refs = Referral.joins(:job)
          .joins(:referee)
          .joins(:referrer)
          .where("jobs.poster_id= ?", user.contact_id)
          .order(created_at: :desc) 
          .eager_load(:job, :referee, :referrer)

Here were my other attempts.

这是我的其他尝试。

#first attempt
#refs = Referral.joins(:job)
#          .where("jobs.poster_id= ?", user.contact_id)
#          .select("referrals.*, jobs.*")
# works, but each column needs to be explicitly referenced to be used later.
# also there are conflicts for columns with the same name like id

#second attempt
#refs = ActiveRecord::Base.connection.exec_query("SELECT jobs.id AS job_id, jobs.*, referrals.id as referral_id, referrals.* FROM referrals INNER JOIN jobs ON job_id = referrals.job_id WHERE (jobs.poster_id=#{user.contact_id});")
# this worked OK, but returned back a funky object, plus the column name
# conflict from the previous method remains an issue.


#third attempt using a view + rails_db_views
#refs = JobReferral.where(:poster_id => user.contact_id)
# this worked well. Unfortunately I couldn't use the SQL statement from above
# instead of jobs.* I had to explicitly alias and name each column.
# Additionally it brought back a ton of duplicate data that I was putting
# into an array when really it is nice to work with ActiveRecord objects.

#eager_load
#refs = Referral.joins(:job)
#          .where("jobs.poster_id= ?", user.contact_id)
#          .eager_load(:job)
# this was my base attempt that worked before I added in two more joins :)

回答by richie

I have got round this problem by creating a VIEW in the database which is the join, and then referencing that as if it were a normal ActiveRecord table in the code. This is fine for getting data out of the database, but if you need to update it, then you'll need to go back to the base classes that represent the 'real' tables. I have found this method to be handy when doing reports that use biggish tables - you can get the data out all in one hit. I am surprised that this doesn't seem to be built into ActiveRecord, seems an obvious thing to me!

我通过在作为连接的数据库中创建一个 VIEW 来解决这个问题，然后在代码中引用它，就好像它是一个普通的 ActiveRecord 表一样。这对于从数据库中获取数据很好，但是如果您需要更新它，那么您将需要返回到代表“真实”表的基类。我发现这种方法在制作使用大表的报告时很方便 - 您可以一键获取所有数据。我很惊讶这似乎没有内置到 ActiveRecord 中，这对我来说似乎很明显！

So for you:

所以对你来说：

IN SQL:

在 SQL 中：

CREATE VIEW User_Profiles
AS
SELECT P.*, U.first_name
FROM Users U
inner join Profiles P on U.id=P.user_id

IN RUBY models file:

在 RUBY 模型文件中：

class UserProfile < ActiveRecord::Base
  self.primary_key = :id 
  #same dependencies as profiles
end

**HINT... I always forget to set the owner of the view (I use postgres), so it blows up straight away with much cursing and self-recrimination.

**提示...我总是忘记设置视图的所有者（我使用 postgres），因此它立即爆发并伴随着大量的诅咒和自责。