Added Gitlab::SQL::Union class

This class can be used to join multiple AcitveRecord::Relation objects
together using a SQL UNION statement. ActiveRecord < 5.0 sadly doesn't
support UNION and existing Gems out there don't handle prepared
statements (e.g. they never incremented the variable bindings).

2 files changed, 50 insertions, 0 deletions

diff --git a/lib/gitlab/sql/union.rb b/lib/gitlab/sql/union.rb
new file mode 100644
index 00000000000..1a62eff0b31
--- /dev/null
+++ b/lib/gitlab/sql/union.rb
@@ -0,0 +1,34 @@
+module Gitlab
+  module SQL
+    # Class for building SQL UNION statements.
+    #
+    # ORDER BYs are dropped from the relations as the final sort order is not
+    # guaranteed any way.
+    #
+    # Example usage:
+    #
+    #     union = Gitlab::SQL::Union.new(user.personal_projects, user.projects)
+    #     sql   = union.to_sql
+    #
+    #     Project.where("id IN (#{sql})")
+    class Union
+      def initialize(relations)
+        @relations = relations
+      end
+
+      def to_sql
+        # Some relations may include placeholders for prepared statements, these
+        # aren't incremented properly when joining relations together this way.
+        # By using "unprepared_statements" we remove the usage of placeholders
+        # (thus fixing this problem), at a slight performance cost.
+        fragments = ActiveRecord::Base.connection.unprepared_statement do
+          @relations.map do |rel|
+            "(#{rel.reorder(nil).to_sql})"
+          end
+        end
+
+        fragments.join(' UNION ')
+      end
+    end
+  end
+end
diff --git a/spec/lib/gitlab/sql/union_spec.rb b/spec/lib/gitlab/sql/union_spec.rb
new file mode 100644
index 00000000000..976360af9b5
--- /dev/null
+++ b/spec/lib/gitlab/sql/union_spec.rb
@@ -0,0 +1,16 @@
+require 'spec_helper'
+
+describe Gitlab::SQL::Union do
+  describe '#to_sql' do
+    it 'returns a String joining relations together using a UNION' do
+      rel1  = User.where(email: 'alice@example.com')
+      rel2  = User.where(email: 'bob@example.com')
+      union = described_class.new([rel1, rel2])
+
+      sql1 = rel1.reorder(nil).to_sql
+      sql2 = rel2.reorder(nil).to_sql
+
+      expect(union.to_sql).to eq("(#{sql1}) UNION (#{sql2})")
+    end
+  end
+end