Skip to content

Instantly share code, notes, and snippets.

@tlowrimore
Created March 10, 2016 17:41
Show Gist options
  • Save tlowrimore/f0e3eca483395de3d373 to your computer and use it in GitHub Desktop.
Save tlowrimore/f0e3eca483395de3d373 to your computer and use it in GitHub Desktop.
Extends the functionality set forth in union_scope.rb, to include EXCEPT and INTERSECT operations
module ActiveRecord
module Scopes
module SetOperations
extend ActiveSupport::Concern
class_methods do
def union_scope(*scopes)
apply_operation 'UNION', scopes
end
def intersect_scope(*scopes)
apply_operation 'INTERSECT', scopes
end
def except_scope(*scopes)
apply_operation 'EXCEPT', scopes
end
private
def apply_operation(operation, scopes)
id_column = "#{table_name}.#{primary_key}"
sub_query = scopes
.map { |s| s.select(id_column).to_sql }
.join(" #{operation} ")
where "#{id_column} IN (#{sub_query})"
end
end
end
end
end
@moveson
Copy link

moveson commented Apr 18, 2016

This looks like just what I need...but I can't figure out how to include it. I've added this file under app/models/concerns. In the models where I need it I have tried include SetOperations, include Scopes::SetOperations, and include ActiveRecord::Scopes::SetOperations. None of the above works. Is this designed to go in the models/concerns folder or somewhere else? Thanks in advance for your help.

EDIT: I've installed in /lib/set_operations.rb. I have included the file with include SetOperations in a couple of ActiveRecord classes, and I call the methods using the ActiveRecord class name, e.g., Participant.union_scope(scopes).

@moveson
Copy link

moveson commented May 23, 2016

I'm using this with some success but have added a bit of nil handling to avoid generating bogus SQL when one (or all) scopes are empty:

    def apply_operation(operation, scopes)
      id_column = "#{table_name}.#{primary_key}"
      sub_query = scopes
                      .map { |s| s.blank? ? nil : s.select(id_column).to_sql }
                      .compact
                      .join(" #{operation} ")

      return none if sub_query.blank?

      where "#{id_column} IN (#{sub_query})"
    end

@gazeldx
Copy link

gazeldx commented Oct 12, 2019

For how to use it, please refer: https://stackoverflow.com/questions/6686920/activerecord-query-union

And you may need to change def union_scope(*scopes) to def union_scope(scopes) to run it successfully.

@dmitry
Copy link

dmitry commented Dec 17, 2019

Less problems and easier to follow:

    def union_scope(*scopes)
      scopes.inject(self) { |all, scope| all.or(where(id: scope)) }
    end

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment