sql - Rails, Ransack: How to search HABTM relationship for "all" matches instead of "any"

Question

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:

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

Photo 1: A, B, C

Photo 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

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.

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.

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.

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

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?

---

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.

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

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 1

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

---

UPDATE

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.

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?