Convert ActiveRecord habtm query to Arel

Question

I have a pretty common habtm relationship:

Photo has_and_belongs_to_many :tags
Tag has_and_belongs_to_many :photos

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

This works as expected.

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)

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.

Update: Just to be extra-specific here, I'm looking to return an Arel node, because I'm working with a gem (ransack) that expects you to pass it Arel nodes for search methods. Ransack will chain this Arel node with others in generating complex search queries.

Could an Arel guru show me how do this correctly?

Answer 1

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))
)

This results in an Arel::Nodes::In instance that you should be able to use directly as in Photo.where(q).

---

UPDATE:

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

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!