Getting the intersection of two active record relations, rails

Question

In this rails app, I have a method called by a controller that takes an active record relation of sites and an array of filters, the method returns any sites in the relation that have all of the filters in the array. Here is the method:

def filter_sites(sites, filters)

  if filters.count > 0
    filterable = sites.tag_join
    filters.each { |f| sites = sites & filterable.with_tag(f) }
  end 

  return sites
end

Here are the scope that are used:

def self.tag_join
  joins(:tags).distinct
end

def self.with_tag(tag_id)
  where('sites_tags.tag_id = ?',tag_id)
end

This is functioning correctly but the problem is that it returns an array. My question is; is there a more effective way or writing this method and returning an active record relation? As later on in the code, further queries need to be chained.

Any help greatly appreciated.

One way you could do this - but which is ugly af - is to use SQL `INTERSECT`. You basically construct an otherwise identical query for each tag and then `INTERSECT` them. This is awful because you have to do it manually, but it works and returns an ActiveRecord object which you can chain on. I also have to ask: couldn't you do `sites.where('sites_tags.tag_id = ?', filters[0]).where('sites_tags.tag_id = ?', filters[1]).where(...)`? — henrebotha, Jul 23 '16 at 18:34
Isn't that just as ugly? Its a variable length array of tags so this would have to be written with a loop. I would have thought theirs a rails way of doing this, its quite common functionality... — Phil Brockwell, Jul 24 '16 at 09:31

score 2 · Answer 1 · edited Dec 31 '16 at 10:02

2

You could do a merge. An array will be returned but it is a clean call, convenient and easy to maintain.

edited Dec 31 '16 at 10:02

KernelPanic

2,328
7
47
90

answered Dec 31 '16 at 07:42

s1mpl3

1,456
1
10
14

score 2 · Answer 2 · answered Mar 22 '22 at 08:44

You might be able to use sql directly with intersect like this:

filters.each do |f| 
  sql = "(#{sites.to_sql} intersect #{filterable.with_tag(f).to_sql}) as sites"
  sites = Site.from(sql)
end

(assuming your class name is Site)

I am not sure how this will go as the number of intersections piles up, but I've used it to solve a similar problem in my code when I have only 2 AR relations.

Getting the intersection of two active record relations, rails

2 Answers2