Rails的加入，其中包括列从联接表 [英] Rails Joins and include columns from joins table

问题描述

我不明白如何让我想从滑轨的列。我有两个模型 - 一个用户和一个配置文件。一个用户：的has_many配置文件（因为用户可以返回到他们的个人资料的早期版本）：

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):

> 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    |                |
+----------------+--------------+------+-----+---------+----------------+

＆NBSP;

 

> 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    |                |
|      .                .          .      .       .             .       |
|      .                .          .      .       .             .       |
|      .                .          .      .       .             .       |
+----------------+--------------+------+-----+---------+----------------+

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

In SQL, I can run the query:

> 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          | ... |
+----+-----------+----------+---------------------+---------+---------------+-----+

看我怎么弄的所有列的两个表连接在一起？然而，当我运行的Rails该查询，我没有得到我想要的列 - 我只得到那些来自简介：

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:

# 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?

我认为这个问题有什么做的事实资料模型没有对用户的属性...

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

推荐答案

我不认为你可以加载用户配置文件，并与参加Rails的。我认为，在早期版本的Rails（小于2.1）加载相关模型做与联接，但它是没有效率。 这里你有一些解释，并链接到其他材料。

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. Here you have some explanation and links to other materials.

所以，即使你explicite说你想加入，Rails会不会将其映射到相应的模式。所以，如果你说的 Profile.whatever_here 它总是会被映射到简介对象。

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_here it will always be mapped to Profile object.

如果你仍然想要做你所说的话有问题，那么你就可以自行调用自定义的SQL查询和处理结果：

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

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

和得到的结果逐行有：

p.fetch_row

这将已经mappet到一个数组中。

It will already be mappet to an array.

您错误是因为你调用 FIRST_NAME 和用户的方法 AciveRecord ::关联对象，并将它存储了档案数组对象，而不是一个单一的对象。因此，

Your errors are because you are calling first_name and user method on AciveRecord::Relation object and it stores an array of Profile objects, not a single object. So

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

768,16工作。

shoud work.

更好的办法来获取只有一个记录就是调用：

Better way to fetch only one record is to call:

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

但是，当你调用 p.user 它将查询数据库。为了避免它，你可以使用包括，但如果你只加载一个配置文件对象，它是无用的。它会有所作为，如果您一次加载许多配置文件和要inlcude用户表。

But when you call p.user it 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.

这篇关于Rails的加入，其中包括列从联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！