Experiment with array column for label titlesexperiment-with-array-columm-for-labels

4 files changed, 43 insertions, 3 deletions

diff --git a/app/models/concerns/issuable.rb b/app/models/concerns/issuable.rb
index 127430cc68f..d6c60f4cc81 100644
--- a/app/models/concerns/issuable.rb
+++ b/app/models/concerns/issuable.rb
@@ -242,9 +242,9 @@ module Issuable
 
     def with_label(title, sort = nil)
       if title.is_a?(Array) && title.size > 1
-        joins(:labels).where(labels: { title: title }).group(*grouping_columns(sort)).having("COUNT(DISTINCT labels.title) = #{title.size}")
+        where('label_titles @> ARRAY[?]', title)
       else
-        joins(:labels).where(labels: { title: title })
+        where(labels: { title: title })
       end
     end
 
diff --git a/db/migrate/20190527135323_add_array_column_for_labels.rb b/db/migrate/20190527135323_add_array_column_for_labels.rb
new file mode 100644
index 00000000000..177ccee5859
--- /dev/null
+++ b/db/migrate/20190527135323_add_array_column_for_labels.rb
@@ -0,0 +1,10 @@
+class AddArrayColumnForLabels < ActiveRecord::Migration[5.1]
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  def change
+    add_column :issues, :label_titles, :text, array: true
+    add_column :merge_requests, :label_titles, :text, array: true
+  end
+end
diff --git a/db/migrate/20190527135836_fill_array_column_for_labels.rb b/db/migrate/20190527135836_fill_array_column_for_labels.rb
new file mode 100644
index 00000000000..d40bd1c4e85
--- /dev/null
+++ b/db/migrate/20190527135836_fill_array_column_for_labels.rb
@@ -0,0 +1,28 @@
+class FillArrayColumnForLabels < ActiveRecord::Migration[5.1]
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  def up
+    update!('issues')
+    update!('merge_requests')
+  end
+
+  private
+
+  def update!(table)
+    execute <<-SQL
+      UPDATE #{table}
+      SET label_titles = subquery.label_titles
+      FROM
+        (
+          SELECT #{table}.id AS id, array_agg(labels.title) AS label_titles
+          FROM #{table}
+          LEFT JOIN label_links ON target_id = #{table}.id AND label_links.target_type = 'Issue'
+          LEFT JOIN labels ON label_id = labels.id
+          GROUP BY #{table}.id
+        ) AS subquery
+      WHERE #{table}.id = subquery.id;
+    SQL
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index 412b5313b69..78e30f72db5 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -10,7 +10,7 @@
 #
 # It's strongly recommended that you check this file into your version control system.
 
-ActiveRecord::Schema.define(version: 20190516011213) do
+ActiveRecord::Schema.define(version: 20190527135836) do
 
   # These are extensions that must be enabled in order to support this database
   enable_extension "plpgsql"
@@ -1089,6 +1089,7 @@ ActiveRecord::Schema.define(version: 20190516011213) do
     t.datetime_with_timezone "closed_at"
     t.integer "closed_by_id"
     t.integer "state_id", limit: 2
+    t.text "label_titles", array: true
     t.index ["author_id"], name: "index_issues_on_author_id", using: :btree
     t.index ["closed_by_id"], name: "index_issues_on_closed_by_id", using: :btree
     t.index ["confidential"], name: "index_issues_on_confidential", using: :btree
@@ -1347,6 +1348,7 @@ ActiveRecord::Schema.define(version: 20190516011213) do
     t.boolean "squash", default: false, null: false
     t.boolean "allow_maintainer_to_push"
     t.integer "state_id", limit: 2
+    t.text "label_titles", array: true
     t.index ["assignee_id"], name: "index_merge_requests_on_assignee_id", using: :btree
     t.index ["author_id"], name: "index_merge_requests_on_author_id", using: :btree
     t.index ["created_at"], name: "index_merge_requests_on_created_at", using: :btree