Rails、Ransack:如何搜索“所有"的 HABTM 关系匹配而不是“任何" [英] Rails, Ransack: How to search HABTM relationship for "all" matches instead of "any"

问题描述

我想知道是否有人有使用 Ransack 与 HABTM 关系的经验.我的应用程序有 photos，它们与 terms 有 habtm 关系(术语就像标签).以下是我遇到的情况的简化说明:

I'm wondering if anyone has experience using Ransack with HABTM relationships. My app has photos which have a habtm relationship with terms (terms are like tags). Here's a simplified explanation of what I'm experiencing:

我有两张照片:照片 1 和照片 2.它们有以下条款:

I have two photos: Photo 1 and Photo 2. They have the following terms:

照片 1:A、B、C

照片 2:A、B、D

我构建了一个搜索表单，并在搜索表单中为所有术语设置了复选框，如下所示:

I built a ransack form, and I make checkboxes in the search form for all the terms, like so:

- terms.each do |t|
  = check_box_tag 'q[terms_id_in][]', t.id

如果我使用:q[terms_id_in][] 并且我选中A，C"，我的结果是照片 1 和照片 2.我只想要照片 1，因为我要求的是 A 和 C，在这个查询中，我不关心 B 或 D，但我希望 A 和 C 都出现在给定的结果中.

If I use: q[terms_id_in][] and I check "A, C" my results are Photo 1 and Photo 2. I only want Photo 1, because I asked for A and C, in this query I don't care about B or D but I want both A and C to be present on a given result.

如果我使用 q[terms_id_in_all][] 我的结果是零，因为两张照片都只包含 A 和 C.或者，也许，因为每个连接只有一个术语，所以没有连接匹配两者A 和 C.不管怎样，我只想退回照片 1.

If I use q[terms_id_in_all][] my results are nil, because neither photo includes only A and C. Or, perhaps, because there's only one term per join, so no join matches both A and C. Regardless, I want just Photo 1 to be returned.

如果我使用任何种类的 q[terms_id_eq][] 我永远不会得到任何结果，所以我认为在这种情况下不起作用.

If I use any variety of q[terms_id_eq][] I never get any results, so I don't think that works in this case.

那么，给定一个 habtm 连接，你如何搜索匹配给定值的模型而忽略未给定的值?

So, given a habtm join, how do you search for models that match the given values while ignoring not given values?

或者，对于任何不熟悉 Ransack 的 rails/sql 专家，您还能如何为具有 habtm 连接的模型创建像我描述的那样的搜索表单?

Or, for any rails/sql gurus not familiar with Ransack, how else might you go about creating a search form like I'm describing for a model with a habtm join?

更新:根据相关问题的答案，我现在已经构建了一个正确匹配的 Arel 查询.不知何故，您应该能够将 Arel 节点用作掠夺者，或者正如 cdesrosiers 指出的那样，用作自定义谓词，但到目前为止我还没有做到这一点.

Update: per the answer to related question, I've now gotten as far as constructing an Arel query that correctly matches this. Somehow you're supposed to be able to use Arel nodes as ransackers, or as cdesrosiers pointed out, as custom predicates, but thus far I haven't gotten that working.

根据那个答案，我设置了以下 ransack 初始值设定项:

Per that answer, I setup the following ransack initializer:

Ransack.configure do |config|
  config.add_predicate 'has_terms',
    :arel_predicate => 'in',
    :formatter => proc {|term_ids| Photo.terms_subquery(term_ids)},
    :validator => proc {|v| v.present?},
    :compounds => true
end

...然后在照片上设置以下方法:

... and then setup the following method on Photo:

def self.terms_subquery(term_ids)
  photos = Arel::Table.new(:photos)
  terms = Arel::Table.new(:terms)
  photos_terms = Arel::Table.new(:photos_terms)
  photos[:id].in(
  photos.project(photos[:id])
    .join(photos_terms).on(photos[:id].eq(photos_terms[:photo_id]))
    .join(terms).on(photos_terms[:term_id].eq(terms[:id]))
    .where(terms[:id].in(term_ids))
    .group(photos.columns)
    .having(terms[:id].count.eq(term_ids.length))
  ).to_sql
end

不幸的是，这似乎不起作用.虽然 terms_subquery 生成正确的 SQL，但 Photo.search(:has_terms => [2,5]).result.to_sql 的结果只是 "SELECT"照片".* FROM "照片" "

Unfortunately this doesn't seem to work. While terms_subquery produces the correct SQL, the result of Photo.search(:has_terms => [2,5]).result.to_sql is just "SELECT "photos".* FROM "photos" "

推荐答案

使用我的 answer 对于您的相关问题，只需对您的标记进行简单更改即可:

With a custom ransack predicate defined as in my answer to your related question, this should work with a simple change to your markup:

- terms.each do |t|
  = check_box_tag 'q[id_has_terms][]', t.id

<小时>

更新

:formatter 并没有按照我的想法去做，并且看到 Ransack 存储库如何没有提到子查询"，您可能无法将其用于您的目的毕竟，重新尝试做.所有可用的选项似乎都用完了，所以除了猴子补丁之外别无他法了.

The :formatter doesn't do what I thought, and seeing as how the Ransack repo makes not a single mention of "subquery," you may not be able to use it for what you're trying to do, after all. All available options seem to be exhausted, so there would be nothing left to do but monkey patch.

为什么不像通常使用活动记录(甚至使用您现在拥有的 Arel 查询)那样跳过搜索并查询照片"表?您已经知道查询有效.您是否希望从使用 Ransack 中获得特定的好处?

Why not just skip ransack and query the "photos" table as you normally would with active record (or even with the Arel query you now have)? You already know the query works. Is there a specific benefit you hoped to reap from using Ransack?

这篇关于Rails、Ransack:如何搜索“所有"的 HABTM 关系匹配而不是“任何"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！