Ruby-on-rails ActiveRecord - 查询多态关联

ActiveRecord - querying polymorphic associations

提问by Toby Hede

I am using polymorphic associations to track Comments in my project. All very straight forward stuff.

我正在使用多态关联来跟踪我项目中的评论。所有非常直接的东西。

The problem I have is in querying based on the polymorphic association and joining from the Comment model back to it's owner.

我遇到的问题是基于多态关联进行查询并从 Comment 模型连接回它的所有者。

So ...

所以 ...

I have a Comment model

我有一个评论模型

class Comment < ActiveRecord::Base
  belongs_to :commentable, :polymorphic => true
end

And a ForumTopics mode:

还有一个论坛主题模式：

class ForumTopic < ActiveRecord::Base
  has_many :comments, :as => :commentable
end

I have several other "commentable" models that aren't important right now. All of this works.

我还有其他几个现在不重要的“可评论”模型。所有这些都有效。

What I am trying to do is find all of the Comments that belong to a ForumTopic with a specified condition (in this case, 'featured' == true).

我想要做的是找到所有属于具有指定条件的 ForumTopic 的评论（在这种情况下，'featured' == true）。

When I try and use a finder to join the models:

当我尝试使用查找器加入模型时：

@comments = Comment.find(:all 
            :joins => :commentable
            :conditions => ["forum_topics.featured = ? ", true] 
            )

I receive the following error:

我收到以下错误：

Can not eagerly load the polymorphic association :commentable

不能急切地加载多态关联：可评论

Using the AR "include syntax":

使用 AR“包含语法”：

@comments = Comment.find(:all 
            :include => :forum_topics
            :conditions => ["forum_topics.featured = ? ", true] 
            )

returns:

返回：

Association named 'forum_topics' was not found; perhaps you misspelled it?

未找到名为“forum_topics”的关联；也许你拼错了？

If I try and join with a table name instead of the association name (string instead of symbol):

如果我尝试使用表名而不是关联名（字符串而不是符号）加入：

@comments = Comment.find(:all,
            :joins => "forum_topics",
            :conditions => ["forum_topics.featured = ? ", true] 
            )

I see:

我懂了：

Mysql::Error: Unknown table 'comments': SELECT comments.FROM comments forum_topics WHERE (forum_topics.featured = 1 )*

Mysql::Error: Unknown table 'comments': SELECT 注释。从评论 forum_topics WHERE (forum_topics.featured = 1 )*

(You can see here that the syntax of the underlying query is totally off and the join is missing altogether).

（您可以在此处看到底层查询的语法完全关闭，并且完全缺少连接）。

Not sure if what I am doing is even possible, and there are other ways to achieve the required result but it seems like it shouldbe doable.

不确定我正在做的事情是否可行，还有其他方法可以实现所需的结果，但似乎应该可行。

Any ideas? Anything I am missing?

有任何想法吗？我缺少什么吗？

采纳答案by Toby Hede

Argh!

啊！

I think I found the problem.

我想我发现了问题。

When joining via:

通过以下方式加入时：

@comments = Comment.find(:all,
        :joins => "forum_topics",
        :conditions => ["forum_topics.featured = ? ", true] 
        )

You need the whole join!

你需要整个加入！

:joins => "INNER JOIN forum_topics ON forum_topics.id = comments.commentable_id",

See the ever-awesome: http://guides.rubyonrails.org/active_record_querying.html#joining-tables

看到令人敬畏的：http: //guides.rubyonrails.org/active_record_querying.html#joining-tables

回答by Sam Peacey

An old question, but there is a cleaner way of achieving this by setting up a direct association for the specific type along with the polymorphic:

一个老问题，但有一种更简洁的方法可以通过为特定类型和多态设置直接关联来实现这一点：

#comment.rb
class Comment < ActiveRecord::Base

  belongs_to :commentable, polymorphic: true
  belongs_to :forum_topics, -> { where( comments: { commentable_type: 'ForumTopic' } ).includes( :comments ) }, foreign_key: 'commentable_id'

  ...

end

You are then able to pass :forum_topicsto includesgetting rid of the need for a messy join:

然后，您可以通过:forum_topics向includes摆脱需要一个凌乱的连接：

@comments = Comment
  .includes( :forum_topics )
  .where( :forum_topics => { featured: true } )

You could then further clean this up by moving the query into a scope:

然后，您可以通过将查询移动到范围中来进一步清理它：

#comment.rb
class Comment < ActiveRecord::Base

  ...

  scope :featured_topics, -> { 
    includes( :forum_topics )
    .where( :forum_topics => { featured: true } ) 
  }

  ...

end

Leaving you to be able to simply do

让你能够简单地做

@comments = Comment.featured_topics

回答by Joshua Pinter

You Need a Conditional, Plus Rails 3+

你需要一个有条件的，加上 Rails 3+

A lot of people alluded to it in the answers and comments but I felt that people, including myself, would get tripped up if I landed here and didn't read thoroughly enough.

很多人在答案和评论中都提到了它，但我觉得如果我来到这里并且没有足够彻底地阅读，人们，包括我自己，都会被绊倒。

So, here's the proper answer, including the conditional that is absolutelynecessary.

所以，这是正确的答案，包括绝对必要的条件。

@comments = Comment.joins( "INNER JOIN forum_topics ON comments.commentable_id = forum_topics.id" )
                   .where( comments:     { commentable_type: 'ForumTopic' } )
                   .where( forum_topics: { featured:         true         } )

Thanks to all, especially @Jits, @Peter, and @prograils for their comments.

感谢所有人，尤其是@Jits、@Peter 和@prograils 的评论。

回答by Joshua Pinter

The accepted solution does not work once you introduce another model that has an association using "commentable". commentable_id is not unique and therefore you'll start retrieving the wrong comments.

一旦您使用“可评论”引入另一个具有关联的模型，接受的解决方案就不起作用。commentable_id 不是唯一的，因此您将开始检索错误的评论。

For example:

例如：

You decide to add a news model that accepts comments...

您决定添加一个接受评论的新闻模型...

class News < ActiveRecord::Base
   has_many :comments, :as => :commentable
end

Now you may get two records back if you made a comment on a forum_topic with an id of 1 and a news article with an id of 1 using your query:

现在，如果您使用查询对 id 为 1 的 forum_topic 和 id 为 1 的新闻文章发表评论，您可能会得到两条记录：

:joins => "INNER JOIN forum_topics ON forum_topics.id = comments.commentable_id"

You could probably solve the problem by supplying a commentable_type as one of your conditions, but I don't think that's the best way to approach this issue.

您可以通过提供 commentable_type 作为您的条件之一来解决问题，但我认为这不是解决此问题的最佳方法。

回答by prograils

Checked to work under Rails 5:

检查在Rails 5下工作：

Solution 1:

解决方案1：

@comments = Comment
              .where(commentable_type: "ForumTopic")
              .joins("INNER JOIN forum_topics ON comments.commentable_id = forum_topics.id")
              .where(forum_topics: {featured: true})
              .all

or

或者

Solution 2:

解决方案2：

@comments = Comment
              .joins("INNER JOIN forum_topics ON comments.commentable_id = forum_topics.id AND comments.commentable_type = 'ForumTopic'")
              .where(forum_topics: {featured: true}).all

Pay attention to the raw SQL syntax: no backticks are allowed. See http://guides.rubyonrails.org/active_record_querying.html#joining-tables.

注意原始 SQL 语法：不允许使用反引号。请参阅http://guides.rubyonrails.org/active_record_querying.html#joining-tables。

I personally prefer Solution 1 as it contains fewer raw SQL syntax.

我个人更喜欢解决方案 1，因为它包含较少的原始 SQL 语法。

回答by Chris Barnes

I came across this post and it lead me to my solution. Using the commentable_type as one of my conditions but using a LEFT OUTER JOIN instead. That way forum topics without comments will be included.

我遇到了这篇文章，它引导我找到了我的解决方案。使用 commentable_type 作为我的条件之一，但使用 LEFT OUTER JOIN 代替。这样，没有评论的论坛主题将被包括在内。

LEFT OUTER JOIN `comments` ON `comments`.`commentable_id` = `forum_topics`.`id` AND `comments`.`commentable_type` = 'ForumTopic'

回答by Damian Sia

Rails does not include a polymorphic join by default but this gem would help you to joins your polymorphic relationship with ease. https://github.com/jameshuynh/polymorphic_join

Rails 默认不包含多态连接，但这个 gem 可以帮助您轻松连接多态关系。https://github.com/jameshuynh/polymorphic_join