Rails 连接并包含连接表中的列 [英] Rails Joins and include columns from joins table

问题描述

我不明白如何从 rails 获取我想要的列.我有两个模型 - 一个用户和一个配置文件.用户 :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    |                |
+----------------+--------------+------+-----+---------+----------------+

 

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

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

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

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

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 的属性有关...

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.

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

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

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

It will already be mappet to an array.

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

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

应该可以.

只获取一条记录的更好方法是调用:

Better way to fetch only one record is to call:

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

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

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屋！