将 ActiveRecord habtm 查询转换为 Arel [英] Convert ActiveRecord habtm query to Arel

问题描述

我有一个很常见的habtm关系:

I have a pretty common habtm relationship:

Photo has_and_belongs_to_many :tags
Tag has_and_belongs_to_many :photos

在我的照片模型中，我有一个带标签"的方法，我用它来查找用一组给定的 tag_id 标记的照片.此查询只需要匹配具有所有给定标签的照片，但不考虑是否存在任何其他标签.这是我的方法:

In my Photo model I've got a method "with tags" that I use to find a photo that is tagged with a given set of tag_ids. This query needs to match only photos that have all of the given tags, but disregarding the presence or lack of any other tags. Here's my method:

def self.with_terms( array )
  select('distinct photos.*').joins(:tags).where('tags.id' => array).group("photos." + self.column_names.join(', photos.')).having("count(*) = #{array.size}")
end

这按预期工作.

现在，为了将它与我正在使用的其他一些库更好地集成，我需要在 Arel 中重新编写它.(让它成为 Arel 节点?，不确定你通常怎么称呼它).

Now, in order to integrate this better with some other libraries I'm using, I need to re-write this in Arel. (make it an Arel node?, not sure what you normally call this).

我一直在试验这个，但老实说我以前从未尝试过使用 Arel，所以我有点迷茫.我一直在控制台中进行试验并尝试过:

I've been experimenting with this, but to be honest I've never tried to use Arel before, so I'm a little lost. I've been experimenting in the console and tried:

t = Photo.arel_table
q = t.join(:tags).on(t[:tags_id].in(array))
Photo.where(q)

但是，(1) 我首先不认为 q 是正确的查询，并且 (2) 它创建了一个 Arel::SelectManager，它当传递到 where 调用引发 Cannot visit Arel::SelectManager 时.所以，显然我做错了.

But, (1) I don't think q is the right query in the first place, and (2) it creates an Arel::SelectManager, which when passed to a where call raises Cannot visit Arel::SelectManager. So, obviously I'm doing this wrong.

更新:为了更加具体，我希望返回一个 Arel 节点，因为我正在使用一个 gem(ransack)，它希望您将 Arel 节点传递给搜索方法.Ransack 会将这个 Arel 节点与其他节点链接起来，以生成复杂的搜索查询.

Arel 大师能否告诉我如何正确执行此操作?

Could an Arel guru show me how do this correctly?

推荐答案

很难找到好的 Arel 文档，但是@PhilipC 整理了一些有用的幻灯片，在他对此的回答中引用了问题.

It's hard to find good Arel documentation, but @Philip C has put together some useful slides, referenced in his answer to this question.

以下应该是您要查找的内容:

The following should be what you're looking for:

photos = Arel::Table.new(:photos)
tags = Arel::Table.new(:tags)
photo_tags = Arel::Table.new(:photo_tags)

q = photos[:id].in(
   photos.project(photos[:id])
  .join(photo_tags).on(photos[:id].eql(photo_tags[:photo_id]))
  .join(tags).on(photo_tags[:tag_id].eql(tags[:id]))
  .where(tags[:id].in(array))
  .group(photos.columns)
  .having(tags[:id].count.eq(array.length))
)

这会生成一个 Arel::Nodes::In 实例，您应该可以像在 Photo.where(q) 中那样直接使用它.

This results in an Arel::Nodes::In instance that you should be able to use directly as in Photo.where(q).

更新:

在查看文档和一些 Ransack 的源代码后，似乎没有任何自然的方法来定义涉及子查询的自定义谓词，这在您的情况下是必需的(因为谓词必须适合 where 子句).解决此问题的一种方法可能是利用谓词使用的 :formatter，如下所示:

After looking through the documentation and some of the source for ransack, there doesn't seem to be any natural way to define a custom predicate involving a subquery, which is necessary in your case (because predicates must fit into a where clause). One way to work around this might be to take advantage of the :formatter that your predicate uses as follows:

Ransack.configure do |config|
  config.add_predicate 'with_tag_ids',
                   :arel_predicate => 'in',
                   :formatter => proc {|tag_ids| tags_subquery(tag_ids) },
                   :validator => proc {|v| v.present?},
                   :compounds => true
end

您可以将tags_subquery(tag_ids)定义为一种方法，该方法生成上述arel节点，但将array替换为tag_ids并调用.to_sql 在返回之前对其进行处理(格式化程序需要返回一个字符串，而不是一个节点).

You can define tags_subquery(tag_ids) as a method that generates the arel node as above but replaces array with tag_ids and calls .to_sql on it before returning it (the formatter needs to return a string, not a node).

我还没有尝试过，所以如果它有效，我会很高兴！

I haven't tried this, so I'll be thrilled if it works!

这篇关于将 ActiveRecord habtm 查询转换为 Arel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！