How to create a Ransacker for HABTM searching?

[burlesona]

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 using checkboxes, which does work but not the way I expected:

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][] ...

and I check "A, C", my results are nil, because neither photo includes only A and C. Again, 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?

---

Update: Given the results of my searching, I've found I can write a method that does produce the desired result:

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

Now, I see that you can make "ransackers" which are kind of like the "search methods" from meta_search. However, the specs etc. seem to indicate to me that ransackers are intended to be Arel stuff, which I'm not familiar with. Can anyone point out how you would convert the above method to a ransacker?

[graywh]

I think if you examine the SQL, you'll see that your terms_id_in_all query is failing for a different reason than you think. I expect it's doing a full join, so your data being queried looks like this:

+-------+------+
| Photo | Term |
+-------+------+
| 1     | A    |
| 1     | B    |
| 1     | C    |
| 2     | A    |
| 2     | B    |
| 2     | D    |
+-------+------+

It's not excluding photo 1 because you specified A and C, but not B. It's excluding photo 1 because none of those records matches A and C.

[burlesona]

@graywh I'm a SQL novice, so that's very useful. That said, do you have any insight on how to construct a query that would match the way I'm intending, and esp. insight on how to construct that query using Ransack?

[burlesona]

Ok, if I write the following method:

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

That will find the terms as I'm intending.

The problem is, how can I use this in a form with Ransack? In meta_search there were search methods that one could add to a model, but they don't seem to be present in Ransack. Am I just overlooking them?

[burlesona]

Update, also added to OP: I see that "ransackers" are the new alternative to "search methods". But, how do you convert the above method into the format for a ransacker?

[lunks]

Yes, you are suppose to use Arel on Ransacker. I'm sorry, but I can't seem to figure out how to solve your issue. @ernie, could you provide some light here?

[burlesona]

Ok, so I've gotten as far as understanding that Ransackers replace meta_search search methods. I understand that a ransacker needs to be written in Arel. I can return an Arel node that will query what I'm looking for like so:

term_ids = [2,5] #an array of term ids that we're trying to match, the user selects these in a form.
photos = Arel::Table.new(:photos)
terms = Arel::Table.new(:terms)
photos_terms = Arel::Table.new(:photos_terms)

q = photos[:id].in(
    photos.project(photos[:id])
    .join(photos_terms).on(photos[:id].eq(photos_terms[:photo_id]))
    .join(terms).on(photos_terms[:tag_id].eq(terms[:id]))
    .where(terms[:id].in(term_ids))
    .group(photos.columns)
    .having(terms[:id].count.eq(term_ids.length))
)

If you do Photo.where(q), this returns the correct matches.

I tried to convert this to a Ransacker, but failed. Here's what I attempted:

ransacker :has_terms do
    photos = Arel::Table.new(:photos)
    terms = Arel::Table.new(:terms)
    photos_terms = Arel::Table.new(:photos_terms)

    q = photos[:id].in(
        photos.project(photos[:id])
        .join(photos_terms).on(photos[:id].eq(photos_terms[:photo_id]))
        .join(terms).on(photos_terms[:tag_id].eq(terms[:id]))
        .where(terms[:id].in(term_ids))
        .group(photos.columns)
        .having(terms[:id].count.eq(term_ids.length))
    )
end

If I then do Photo.search(:has_terms_in=>[2,5]) I get undefined local variable term_ids. Of course, because there's no apparent way for me to insert the passed argument into the block.

If I change this to:

ransacker :has_terms do |term_ids|
  ...
end

Then I get undefined method length for active record associations join dependency. So there at least term_ids is getting defined, but not as the array of ids... which still leaves me sol.

Obviously I don't understand what is going on. @ernie , @lunks , @graywh , @radar , if any of you can explain this I would really appreciate it. I need to be able to pass an array of term_ids into a ransack search and have it return photos that match all of the given term_ids. This is just like pretty much any tagging model anyone would use in a rails app, so having this functionality documented seems pretty essential to use Ransack in real world apps. Thank you for your help!

[davidcollom]

@burlesona did you ever get to a solution for this? we are having a very similar issue and wondering what you have done in the mean time?

[burlesona]

@davidcollom My conclusion after investing many, many hours trying to get this to work, was that Ransack is not finished, and simply will not do this kind of search. After digging around further I became even more frustrated with the current state of Ransack, and pulled it from my project entirely. I'm now doing my own search methods which look like this:

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

    def self.with_any_terms( array )
        select('distinct photos.*').joins(:terms).where('terms.id' => array).group("photos." + self.column_names.join(', photos.'))
    end

I feel like these are ugly, but they work, and I was able to get them working in probably 10-15 minutes, which made me want to punch myself in the face for all the hours and hours I spent trying to work Arel magic with ransack to get this functioning.

[jonatack]

@burlesona @davidcollom It works fine building SQL queries with Arel.

[burlesona]

@jonatack I'm no longer working on the project I was two years ago, nor am I using ransack for anything anymore, but since you're closing the issue and saying "habtm searching works", and since this thread has several detailed steps explaining what I was trying to do back then and what didn't work, could you post an example of how you do habtm searching and what does work, so that people who find this thread in the future see an example of how to do it right, not just your comment saying "works ok for me, closed."? Thanks!

[jonatack]

@burlesona The issue is pretty old and isn't really a bug report. It's more of a Stack Overflow question on composing SQL queries via Arel. People can still participate in the discussion or help out :)

Ransack is open source software and maintainer time is limited. My personal priority is keeping Ransack functional as it already was and is, with the moving target that is Active Record/Arel.

Thanks for your understanding 😃

[jonatack]

For example, Rails 4.2 is about to be released, and the Ransack branch for Rails 4.2 is not yet merged into Ransack master with working tests. This sort of thing is the priority to me with the time I have. Hope you understand :)

[burlesona]

@jonatack it is Open Source and certainly you and the other maintainers should work on whatever you want to work on and consider to be most important.

For what it's worth, I would consider this an issue, rather than an SO question, as it's something that users of ransack will want to be able to do, that ransack implies it can do, that in my testing did not work correctly, and that there was no documentation for.

As far as the decision to leave open or to close, certainly you can close the issue for any reason you want. All I'm saying is it would be more informative to close the issue and say: "This now works, see (commit) and (example)," or, "This is not what Ransack is for and we're not going to address it, sorry," or, "This is not a priority right now but we may come back and look at it later." In the first case, assuming this has been fixed at some point in the past two years, taking the time to put a link to where this happens in the source and possibly an example is building up your documentation, which is a valuable part of an OSS project.

But, regardless, as you said it's your free time, not mine, going into this project, and I'm not using it anymore so I don't have a reason to dig back into it and try and figure this out further, which leaves me no room to complain. So, good luck with all your efforts, and thanks for keeping this project going :)

[nateberkopec]

After much pain, suffering, and blood sacrifice, I have discovered the sacred incantation:

I'm using Tags and Packages instead of Terms and Photos, but it's the exact same problem.

In the view:

<%= check_box_tag(
  'q[matches_all_tags][]',
  t.id,
  params["q"] && params["q"].fetch("matches_all_tags", []).include?(t.id.to_s)
  ) %>

In the model:

  scope :matches_all_tags, -> *tag_ids { where(matches_all_tags_arel(tag_ids)) }

  def self.matches_all_tags_arel(tag_ids)
    packages = Arel::Table.new(:packages)
    tags = Arel::Table.new(:tags)
    packages_tags = Arel::Table.new(:packages_tags)

    packages[:id].in(
      packages.project(packages[:id])
        .join(packages_tags).on(packages[:id].eq(packages_tags[:package_id]))
        .join(tags).on(packages_tags[:tag_id].eq(tags[:id]))
        .where(tags[:id].in(tag_ids))
        .group(packages[:id])
        .having(tags[:id].count.eq(tag_ids.length))
    )
  end

  def self.ransackable_scopes(auth_object = nil)
    super + %w(matches_all_tags)
  end

[shreya]

@nateberkopec .. Sir, you saved my life with your solution. Thank you!!

[bo-oz]

As a small critique on this solution, it's not actually checking if a record in table A has the queried related records in table B. It's only checking if the amount of records returned by the query equals the amount of ID's supplied to the query. This could work in some situations, but fails when a record in table A has multiple relationships to the same record in table B. For instance, if record 1 from table A is related twice to record 2 from table B, then searching for record 2 and any other record results in displaying record 1. I'm still looking into fixing this... any help is appreciated.

[bo-oz]

Ok, I discovered that this Arel structure is based on this normal Rails command came up with by @fabn:

Article.search(categories_id_in:[526,527,556])
.result
.group('articles.id')
.having('COUNT(categories.id) = ?', 3)
.to_sql

As I described in the previous command this could lead to unexpected results when 'an Article' is related to the same Category multiple times (not a really logical use-case, but I stumbled upon one that is)

What you would like is to check the uniqueness of the unique count of the category_id in the join table in the 'having' part. So this would probably work:

Article.search(categories_id_in:[526,527,556])
.result
.group('articles.id')
.having('COUNT(DISTINCT(article_categories.category_id)) = ?', 3)
.to_sql

(in which I'm guessing article_categories is the join table name)

I tested this and it seems to work, problem is I do not know how to adapt this into the Arel model... Any ideas?

[midhunkrishna]

to extend on what @nateberkopec mentioned, say I have to filter list of orders by their payment_types, say ['prepay', 'cash_on_delivery'], in ActiveAdmin, I do the following:

filter :payment_type, as: :select, collection: ['prepay', 'cash_on_delivery']

In order model:

class Order < ActiveRecord::Base

  scope :prepay, -> { joins(...).where(...) }
  scope :cash_on_delivery, -> { joins(...).where(...) }

  scope :payment_type, -> (type) { Order.public_send(type) }

  def self.ransackable_scopes(auth_object = nil)
    super + %w(payment_type)
  end
end

[svgr-slth]

Hello there!

I've experienced pretty much the exact same issue than people here, but just a few years later.
By the way thanks @nateberkopec for providing the scope.

Basically my goal is to get my models depending on tags like so:
Models having tags (( A && B) || C) && ! D

Which is basically exactly what the advanced demo form is doing. But the AND part being not really compatible with HABTM or HMT relationships as we know, i ended up here and implemented that ransackable scope. But I'm wondering if its possible to make that scope work with the advanced example. Is there a way i can generate the q parameter so it ends up using this scope alongside other predicates on other fields, and in nested conditions like so, or is it simply impossible ?

I have been trying a lot of stuff implementing new predicates that uses the scope but never found a way to make this work properly generate a valid request in the end.

My solution for now as dirty as it is is to keep a serialized version of the tags i associate to my model, inside the model itself and as a big string, so i can use the contains / contains_all / contains_any predicates to match my tags in that string and still using the nested grouping forms.

But it has an obvious limitation with tags that are similar or containing each other (e.g "north" / "north-east", if i want every model tagged with "north" i end up with those + those tagged "north-east")

If anybody knows if its even possible, or if i can just give up looking for the solution that would already be something.

Ransack is some awesome piece of work, and basically the only open-source gem to make this happen out of the box, anybody wanting to implement a complex tagging system, that can be searchable with advanced queries will want that to work. And that's something you basically discover at the very end of the dev which can get frustrating of course so if not possible at all, i would put a disclaimer on the docs, and any ways i would put one just to make clear this case requires additional work for it to work, because when reading it through at the moment it really seems it's possible out of the box.