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?

This is not an answer, but try Squeel!! ARs defaults are quite lame imo and while AREL is far more powerful, it's woefully documented and not very developer friendly. The squeel gem is quite good for doing things like this! — vvohra87, Nov 19 '12 at 08:33

score 10 · Accepted Answer · edited May 23 '17 at 11:45

10

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!

edited May 23 '17 at 11:45

Community

1
1

answered Nov 18 '12 at 23:51

cdesrosiers

8,862
2
28
33

Awesome -- this query works, but how do you return it as an Arel node so that it can be passed into a `where` clause directly? The reason is I'm trying to write a query method that can be used by a library that relies on Arel, and specifically the method needs to return an Arel node. Ie, it would meet what I'm looking for if `Photo.where(q)` returned the correct result. – Andrew Nov 19 '12 at 01:02
The query in `q` is fairly complex. If you want to force it into a `where` clause, it will involve a subquery. If that's okay with you, one way to achieve this is to change `q` to the form `"photos"."id" IN (subquery returning photo ids)`. See edit above for the equivalent Arel. – cdesrosiers Nov 19 '12 at 02:34
Are you trying to define a custom ransack predicate with this? – cdesrosiers Nov 19 '12 at 21:53
Yes, @cdesrosiers, that is exactly what I'm trying to do. If you can help with that I'll be very grateful. See also question: http://stackoverflow.com/questions/13409627/rails-ransack-how-to-search-habtm-relationship-for-all-matches-instead-of-a – Andrew Nov 19 '12 at 22:14
Ok, I put it on the photo model, and the 'add_predicate' config option in an initializer. This code runs, and the query runs, but for some reason it doesn't return the same results (when used the way your update prescribes) as it does when you run the query by itself. Oh well. I think, given your effort to explain the Arel side of this to me, you've got the points for this question. If you want to keep experimenting with ransack specifically go ahead and do so on the other question. Thanks!!! – Andrew Nov 20 '12 at 02:31
Great query, but I would like to point out that it probably can lead to incorrect results when a record is related to the same record from the related model multiple times since it compares a count of the array.length (so a record having two relations to record 6 shows up when querying for 6 and any other record). Is there some way of fixing this? – bo-oz Nov 24 '15 at 11:25

Convert ActiveRecord habtm query to Arel

1 Answers1

Linked