转换的ActiveRecord的habtm查询阿雷尔 [英] Convert ActiveRecord habtm query to Arel
问题描述
我有一个共同的pretty的的habtm
关系:
I have a pretty common habtm
relationship:
Photo has_and_belongs_to_many :tags
Tag has_and_belongs_to_many :photos
在我的照片模式,我有一个方法的标记,我用它来发现,被打上了一组给定tag_ids的照片。此查询需要匹配只有那些所有给定标签的照片,但不考虑任何其他标记的presence或缺乏。这里是我的方法:
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
这工作正常。
现在,为了整合与我使用一些其他的图书馆这更好,我需要在阿雷尔重新写这个。 (使它成为阿雷尔节点?不知道你通常于召本)。
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).
我一直在尝试这一点,但说实话,我从来没有尝试过使用阿雷尔,所以我有点失落。我一直在尝试在控制台和尝试:
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)我不认为①
是摆在首位的权利查询,以及(2)它创建一个阿雷尔:: SelectManager
,在传递到哪里调用产生无法访问阿雷尔:: 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.
更新:只是要格外特定这里,我期待那希望你把它传递阿雷尔节点的搜索方法返回一个阿雷尔节点,因为我正与宝石(搜查)。洗劫意志链跟别人这个阿雷尔节点产生复杂的搜索查询。的
难道一个阿雷尔大师教我如何正确地做到这一点?
Could an Arel guru show me how do this correctly?
推荐答案
这是很难找到好的阿雷尔的文档,但@ 菲利普Ç已经把一些有用的幻灯片,在他的回答中引用该<一href="http://stackoverflow.com/questions/5688386/where-can-i-find-good-arel-documentation">question.
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))
)
这将导致一个阿雷尔::节点::在
实例,你应该能够直接使用在 Photo.where(Q)
。
This results in an Arel::Nodes::In
instance that you should be able to use directly as in Photo.where(q)
.
更新:
在翻翻文件和部分源洗劫的,似乎没有要定义一个自定义predicate涉及子查询,这是必要的,你的情况(任何自然的方式,因为predicates必须放入一个其中,
子句)。要解决这样的一个方法可能是利用了:格式
您predicate用途如下:
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节点上面,但替换的方法阵列
与 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查询阿雷尔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!