Use a specialized class for querying events

This changes various controllers to use the new EventCollection class
for retrieving events. This class uses a JOIN LATERAL query on
PostgreSQL to retrieve queries in a more efficient way, while falling
back to a simpler / less efficient query for MySQL.

The EventCollection class also includes a limit on the number of events
to display to prevent malicious users from cycling through all events,
as doing so could put a lot of pressure on the database.

JOIN LATERAL is only supported on PostgreSQL starting with version 9.3.0
and as such this optimisation is only used when using PostgreSQL 9.3 or
newer.

6 files changed, 121 insertions, 15 deletions

diff --git a/app/controllers/dashboard/projects_controller.rb b/app/controllers/dashboard/projects_controller.rb
index 74fe45e1ff6..f71ab702e71 100644
--- a/app/controllers/dashboard/projects_controller.rb
+++ b/app/controllers/dashboard/projects_controller.rb
@@ -52,8 +52,10 @@ class Dashboard::ProjectsController < Dashboard::ApplicationController
   end
 
   def load_events
-    @events = Event.in_projects(load_projects(params.merge(non_public: true)))
-    @events = event_filter.apply_filter(@events).with_associations
-    @events = @events.limit(20).offset(params[:offset] || 0)
+    projects = load_projects(params.merge(non_public: true))
+
+    @events = EventCollection
+      .new(projects, offset: params[:offset].to_i, filter: event_filter)
+      .to_a
   end
 end
diff --git a/app/controllers/dashboard_controller.rb b/app/controllers/dashboard_controller.rb
index f9c31920302..19a5db6fd17 100644
--- a/app/controllers/dashboard_controller.rb
+++ b/app/controllers/dashboard_controller.rb
@@ -29,9 +29,9 @@ class DashboardController < Dashboard::ApplicationController
         current_user.authorized_projects
       end
 
-    @events = Event.in_projects(projects)
-    @events = @event_filter.apply_filter(@events).with_associations
-    @events = @events.limit(20).offset(params[:offset] || 0)
+    @events = EventCollection
+      .new(projects, offset: params[:offset].to_i, filter: @event_filter)
+      .to_a
   end
 
   def set_show_full_reference
diff --git a/app/controllers/groups_controller.rb b/app/controllers/groups_controller.rb
index 27137ffde54..f76b3f69e9e 100644
--- a/app/controllers/groups_controller.rb
+++ b/app/controllers/groups_controller.rb
@@ -160,9 +160,9 @@ class GroupsController < Groups::ApplicationController
   end
 
   def load_events
-    @events = Event.in_projects(@projects)
-    @events = event_filter.apply_filter(@events).with_associations
-    @events = @events.limit(20).offset(params[:offset] || 0)
+    @events = EventCollection
+      .new(@projects, offset: params[:offset].to_i, filter: event_filter)
+      .to_a
   end
 
   def user_actions
diff --git a/app/controllers/projects_controller.rb b/app/controllers/projects_controller.rb
index 8dfe0f51709..e93f34498d6 100644
--- a/app/controllers/projects_controller.rb
+++ b/app/controllers/projects_controller.rb
@@ -301,10 +301,11 @@ class ProjectsController < Projects::ApplicationController
   end
 
   def load_events
-    @events = @project.events.recent
-    @events = event_filter.apply_filter(@events).with_associations
-    limit = (params[:limit] || 20).to_i
-    @events = @events.limit(limit).offset(params[:offset] || 0)
+    projects = Project.where(id: @project.id)
+
+    @events = EventCollection
+      .new(projects, offset: params[:offset].to_i, filter: event_filter)
+      .to_a
   end
 
   def project_params
diff --git a/app/models/event.rb b/app/models/event.rb
index a598eb08e82..f2a560a6b56 100644
--- a/app/models/event.rb
+++ b/app/models/event.rb
@@ -62,8 +62,13 @@ class Event < ActiveRecord::Base
   scope :recent, -> { reorder(id: :desc) }
   scope :code_push, -> { where(action: PUSHED) }
 
-  scope :in_projects, ->(projects) do
-    where(project_id: projects.pluck(:id)).recent
+  scope :in_projects, -> (projects) do
+    sub_query = projects
+      .except(:order)
+      .select(1)
+      .where('projects.id = events.project_id')
+
+    where('EXISTS (?)', sub_query).recent
   end
 
   scope :with_associations, -> do
diff --git a/app/models/event_collection.rb b/app/models/event_collection.rb
new file mode 100644
index 00000000000..8b8244314af
--- /dev/null
+++ b/app/models/event_collection.rb
@@ -0,0 +1,98 @@
+# A collection of events to display in an event list.
+#
+# An EventCollection is meant to be used for displaying events to a user (e.g.
+# in a controller), it's not suitable for building queries that are used for
+# building other queries.
+class EventCollection
+  # To prevent users from putting too much pressure on the database by cycling
+  # through thousands of events we put a limit on the number of pages.
+  MAX_PAGE = 10
+
+  # projects - An ActiveRecord::Relation object that returns the projects for
+  #            which to retrieve events.
+  # filter - An EventFilter instance to use for filtering events.
+  def initialize(projects, limit: 20, offset: 0, filter: nil)
+    @projects = projects
+    @limit = limit
+    @offset = offset
+    @filter = filter
+  end
+
+  # Returns an Array containing the events.
+  def to_a
+    return [] if current_page > MAX_PAGE
+
+    relation = if Gitlab::Database.join_lateral_supported?
+                 relation_with_join_lateral
+               else
+                 relation_without_join_lateral
+               end
+
+    relation.with_associations.to_a
+  end
+
+  private
+
+  # Returns the events relation to use when JOIN LATERAL is not supported.
+  #
+  # This relation simply gets all the events for all authorized projects, then
+  # limits that set.
+  def relation_without_join_lateral
+    events = filtered_events.in_projects(projects)
+
+    paginate_events(events)
+  end
+
+  # Returns the events relation to use when JOIN LATERAL is supported.
+  #
+  # This relation is built using JOIN LATERAL, producing faster queries than a
+  # regular LIMIT + OFFSET approach.
+  def relation_with_join_lateral
+    projects_for_lateral = projects.select(:id).to_sql
+
+    lateral = filtered_events
+      .limit(limit_for_join_lateral)
+      .where('events.project_id = projects_for_lateral.id')
+      .to_sql
+
+    # The outer query does not need to re-apply the filters since the JOIN
+    # LATERAL body already takes care of this.
+    outer = base_relation
+      .from("(#{projects_for_lateral}) projects_for_lateral")
+      .joins("JOIN LATERAL (#{lateral}) AS #{Event.table_name} ON true")
+
+    paginate_events(outer)
+  end
+
+  def filtered_events
+    @filter ? @filter.apply_filter(base_relation) : base_relation
+  end
+
+  def paginate_events(events)
+    events.limit(@limit).offset(@offset)
+  end
+
+  def base_relation
+    # We want to have absolute control over the event queries being built, thus
+    # we're explicitly opting out of any default scopes that may be set.
+    Event.unscoped.recent
+  end
+
+  def limit_for_join_lateral
+    # Applying the OFFSET on the inside of a JOIN LATERAL leads to incorrect
+    # results. To work around this we need to increase the inner limit for every
+    # page.
+    #
+    # This means that on page 1 we use LIMIT 20, and an outer OFFSET of 0. On
+    # page 2 we use LIMIT 40 and an outer OFFSET of 20.
+    @limit + @offset
+  end
+
+  def current_page
+    (@offset / @limit) + 1
+  end
+
+  def projects
+    @projects.except(:order)
+  end
+end