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\" "

Spinose

(ransack is truly brilliant!!) I haven't tried this and it may not apply to your case so I won't put it as an answer, but ransack will parse an _or_ between fields, for example q.user_name_or_user_email_cont lets you do a partial match on distinct fields. Checkboxes may not be so easy, but maybe it will lead you to a solution... – Lebron

@Spinose have you solved the issue? As told in many places including ransack repo - ransack got rid of using scopes and the solution seems like scope wrapped inside a custom predicate. Have you got it to work? – Inconvertible

@Inconvertible nope, haven't had any luck. See the comments on the answer below, they're all still valid as of today. – Spinose

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?

Bourgeon

As noted on the related question, the custom predicate runs without raising errors, but doesn't result in the correct matches for some reason. To be honest I don't understand how ransack works, and with no error message I don't know what the problem is in this case. – Spinose

Can you check the logs and post the query that actually gets executed when the search is performed? – Bourgeon

Lol, it doesn't seem to grab the query at all. If I run Photo.search(:has_terms=>[2,5]).result.to_sql, the value is: "SELECT \"photos\".* FROM \"photos\" " Note, I'll add the rest of the setup to the question. – Spinose

Hmmm, judging from ransack's explanation of predicates, it seems that the options hash given to search must be of the form :column_name_predicate in order to build the SQL WHERE clause. So, since you're searching the id column, and you've called the predicate has_terms, try Photo.search(:id_has_terms => [2,5]). – Bourgeon

Also, I think you'll need to remove the wrapper photos[:id].in() from the arel query, leaving just photos.project(photos[:id])..., since that part of the query seems to be coded in the custom predicate definition already. – Bourgeon

Doing it that way results in undefined method terms_subquery' for main:Object`. Geez Ransack, why no documentation!? – Spinose

After changing terms_subquery to Photo.temrs_subquery I get undefined method length on 2:fixnum, so apparently it is now passing the array args to the formatter one at a time? – Spinose

Re: specific reason to use ransack, is I was going to use it for search forms throughout the site, and I want to be able to have check boxes for terms and return matches for all terms. It would be convenient if this could be done in a single form, but I agree at this point I don't think this can be done. – Spinose

+ Answer and Bounty, because you're right, it turns out Ransack can't do "ALL" on a many-to-many join. Bummer. – Spinose

I have this somewhat related question with a bounty. Would you like to take a crack at it? – Lownecked