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_…

Accepted Answer

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!

Convert ActiveRecord habtm query to Arel

Solution

Problem

Related problems