Merge branch 'atom-feed-latest-update' into 'master'

Improve performance of user profiles, finding groups, and finding projects

This MR improves the following:

* Rendering of profile pages and Atom feeds
* Finding groups (using GroupsFinder & friends)
* Finding projects (using ProjectsFinder & friends)

Initially this MR was intended to only improve rendering of Atom feeds, but over time other fixes were introduced as well as the same code was the cause of all these problems.

See merge request !1790

31 files changed, 700 insertions, 215 deletions

diff --git a/CHANGELOG b/CHANGELOG
index cc165e494a5..0a9ef0a9434 100644
--- a/CHANGELOG
+++ b/CHANGELOG
@@ -3,6 +3,8 @@ Please view this file on the master branch, on stable branches it's out of date.
 v 8.3.0 (unreleased)
 
 v 8.2.0
+  - Improved performance of finding projects and groups in various places
+  - Improved performance of rendering user profile pages and Atom feeds
   - Fix grouping of contributors by email in graph.
   - Remove CSS property preventing hard tabs from rendering in Chromium 45 (Stan Hu)
   - Fix Drone CI service template not saving properly (Stan Hu)
diff --git a/app/controllers/users_controller.rb b/app/controllers/users_controller.rb
index 1484356a7f4..30cb869eb2a 100644
--- a/app/controllers/users_controller.rb
+++ b/app/controllers/users_controller.rb
@@ -3,14 +3,11 @@ class UsersController < ApplicationController
   before_action :set_user
 
   def show
-    @contributed_projects = contributed_projects.joined(@user).
-      reject(&:forked?)
+    @contributed_projects = contributed_projects.joined(@user).reject(&:forked?)
 
-    @projects = @user.personal_projects.
-      where(id: authorized_projects_ids).includes(:namespace)
+    @projects = PersonalProjectsFinder.new(@user).execute(current_user)
 
-    # Collect only groups common for both users
-    @groups = @user.groups & GroupsFinder.new.execute(current_user)
+    @groups = JoinedGroupsFinder.new(@user).execute(current_user)
 
     respond_to do |format|
       format.html
@@ -53,16 +50,8 @@ class UsersController < ApplicationController
     @user = User.find_by_username!(params[:username])
   end
 
-  def authorized_projects_ids
-    # Projects user can view
-    @authorized_projects_ids ||=
-      ProjectsFinder.new.execute(current_user).pluck(:id)
-  end
-
   def contributed_projects
-    @contributed_projects = Project.
-      where(id: authorized_projects_ids & @user.contributed_projects_ids).
-      includes(:namespace)
+    ContributedProjectsFinder.new(@user).execute(current_user)
   end
 
   def contributions_calendar
@@ -73,9 +62,13 @@ class UsersController < ApplicationController
   def load_events
     # Get user activity feed for projects common for both users
     @events = @user.recent_events.
-      where(project_id: authorized_projects_ids).
-      with_associations
+      merge(projects_for_current_user).
+      references(:project).
+      with_associations.
+      limit_recent(20, params[:offset])
+  end
 
-    @events = @events.limit(20).offset(params[:offset] || 0)
+  def projects_for_current_user
+    ProjectsFinder.new.execute(current_user)
   end
 end
diff --git a/app/finders/contributed_projects_finder.rb b/app/finders/contributed_projects_finder.rb
new file mode 100644
index 00000000000..0209649b017
--- /dev/null
+++ b/app/finders/contributed_projects_finder.rb
@@ -0,0 +1,37 @@
+class ContributedProjectsFinder
+  def initialize(user)
+    @user = user
+  end
+
+  # Finds the projects "@user" contributed to, limited to either public projects
+  # or projects visible to the given user.
+  #
+  # current_user - When given the list of the projects is limited to those only
+  #                visible by this user.
+  #
+  # Returns an ActiveRecord::Relation.
+  def execute(current_user = nil)
+    if current_user
+      relation = projects_visible_to_user(current_user)
+    else
+      relation = public_projects
+    end
+
+    relation.includes(:namespace).order_id_desc
+  end
+
+  private
+
+  def projects_visible_to_user(current_user)
+    authorized = @user.contributed_projects.visible_to_user(current_user)
+
+    union = Gitlab::SQL::Union.
+      new([authorized.select(:id), public_projects.select(:id)])
+
+    Project.where("projects.id IN (#{union.to_sql})")
+  end
+
+  def public_projects
+    @user.contributed_projects.public_only
+  end
+end
diff --git a/app/finders/groups_finder.rb b/app/finders/groups_finder.rb
index b5f3176461c..91cb0f228f0 100644
--- a/app/finders/groups_finder.rb
+++ b/app/finders/groups_finder.rb
@@ -1,39 +1,44 @@
 class GroupsFinder
-  def execute(current_user, options = {})
-    all_groups(current_user)
+  # Finds the groups available to the given user.
+  #
+  # current_user - The user to find the groups for.
+  #
+  # Returns an ActiveRecord::Relation.
+  def execute(current_user = nil)
+    if current_user
+      relation = groups_visible_to_user(current_user)
+    else
+      relation = public_groups
+    end
+
+    relation.order_id_desc
   end
 
   private
 
-  def all_groups(current_user)
-    group_ids = if current_user
-                  if current_user.authorized_groups.any?
-                    # User has access to groups
-                    #
-                    # Return only:
-                    #   groups with public projects
-                    #   groups with internal projects
-                    #   groups with joined projects
-                    #
-                    Project.public_and_internal_only.pluck(:namespace_id) +
-                      current_user.authorized_groups.pluck(:id)
-                  else
-                    # User has no group membership
-                    #
-                    # Return only:
-                    #   groups with public projects
-                    #   groups with internal projects
-                    #
-                    Project.public_and_internal_only.pluck(:namespace_id)
-                  end
-                else
-                  # Not authenticated
-                  #
-                  # Return only:
-                  #   groups with public projects
-                  Project.public_only.pluck(:namespace_id)
-                end
-
-    Group.where("public IS TRUE OR id IN(?)", group_ids)
+  # This method returns the groups "current_user" can see.
+  def groups_visible_to_user(current_user)
+    base = groups_for_projects(public_and_internal_projects)
+
+    union = Gitlab::SQL::Union.
+      new([base.select(:id), current_user.authorized_groups.select(:id)])
+
+    Group.where("namespaces.id IN (#{union.to_sql})")
+  end
+
+  def public_groups
+    groups_for_projects(public_projects)
+  end
+
+  def groups_for_projects(projects)
+    Group.public_and_given_groups(projects.select(:namespace_id))
+  end
+
+  def public_projects
+    Project.unscoped.public_only
+  end
+
+  def public_and_internal_projects
+    Project.unscoped.public_and_internal_only
   end
 end
diff --git a/app/finders/joined_groups_finder.rb b/app/finders/joined_groups_finder.rb
new file mode 100644
index 00000000000..e7523136fea
--- /dev/null
+++ b/app/finders/joined_groups_finder.rb
@@ -0,0 +1,49 @@
+# Class for finding the groups a user is a member of.
+class JoinedGroupsFinder
+  def initialize(user = nil)
+    @user = user
+  end
+
+  # Finds the groups of the source user, optionally limited to those visible to
+  # the current user.
+  #
+  # current_user - If given the groups of "@user" will only include the groups
+  #                "current_user" can also see.
+  #
+  # Returns an ActiveRecord::Relation.
+  def execute(current_user = nil)
+    if current_user
+      relation = groups_visible_to_user(current_user)
+    else
+      relation = public_groups
+    end
+
+    relation.order_id_desc
+  end
+
+  private
+
+  # Returns the groups the user in "current_user" can see.
+  #
+  # This list includes all public/internal projects as well as the projects of
+  # "@user" that "current_user" also has access to.
+  def groups_visible_to_user(current_user)
+    base  = @user.authorized_groups.visible_to_user(current_user)
+    extra = public_and_internal_groups
+    union = Gitlab::SQL::Union.new([base.select(:id), extra.select(:id)])
+
+    Group.where("namespaces.id IN (#{union.to_sql})")
+  end
+
+  def public_groups
+    groups_for_projects(@user.authorized_projects.public_only)
+  end
+
+  def public_and_internal_groups
+    groups_for_projects(@user.authorized_projects.public_and_internal_only)
+  end
+
+  def groups_for_projects(projects)
+    @user.groups.public_and_given_groups(projects.select(:namespace_id))
+  end
+end
diff --git a/app/finders/personal_projects_finder.rb b/app/finders/personal_projects_finder.rb
new file mode 100644
index 00000000000..a61ffa22990
--- /dev/null
+++ b/app/finders/personal_projects_finder.rb
@@ -0,0 +1,41 @@
+class PersonalProjectsFinder
+  def initialize(user)
+    @user = user
+  end
+
+  # Finds the projects belonging to the user in "@user", limited to either
+  # public projects or projects visible to the given user.
+  #
+  # current_user - When given the list of projects is limited to those only
+  #                visible by this user.
+  #
+  # Returns an ActiveRecord::Relation.
+  def execute(current_user = nil)
+    if current_user
+      relation = projects_visible_to_user(current_user)
+    else
+      relation = public_projects
+    end
+
+    relation.includes(:namespace).order_id_desc
+  end
+
+  private
+
+  def projects_visible_to_user(current_user)
+    authorized = @user.personal_projects.visible_to_user(current_user)
+
+    union = Gitlab::SQL::Union.
+      new([authorized.select(:id), public_and_internal_projects.select(:id)])
+
+    Project.where("projects.id IN (#{union.to_sql})")
+  end
+
+  def public_projects
+    @user.personal_projects.public_only
+  end
+
+  def public_and_internal_projects
+    @user.personal_projects.public_and_internal_only
+  end
+end
diff --git a/app/finders/projects_finder.rb b/app/finders/projects_finder.rb
index c81bb51583a..dd35c215c50 100644
--- a/app/finders/projects_finder.rb
+++ b/app/finders/projects_finder.rb
@@ -1,11 +1,39 @@
 class ProjectsFinder
-  def execute(current_user, options = {})
+  # Returns all projects, optionally including group projects a user has access
+  # to.
+  #
+  # ## Examples
+  #
+  # Retrieving all public projects:
+  #
+  #     ProjectsFinder.new.execute
+  #
+  # Retrieving all public/internal projects and those the given user has access
+  # to:
+  #
+  #     ProjectsFinder.new.execute(some_user)
+  #
+  # Retrieving all public/internal projects as well as the group's projects the
+  # user has access to:
+  #
+  #     ProjectsFinder.new.execute(some_user, group: some_group)
+  #
+  # Returns an ActiveRecord::Relation.
+  def execute(current_user = nil, options = {})
     group = options[:group]
 
     if group
-      group_projects(current_user, group)
+      base, extra = group_projects(current_user, group)
     else
-      all_projects(current_user)
+      base, extra = all_projects(current_user)
+    end
+
+    if base and extra
+      union = Gitlab::SQL::Union.new([base.select(:id), extra.select(:id)])
+
+      Project.where("projects.id IN (#{union.to_sql})")
+    else
+      base
     end
   end
 
@@ -13,77 +41,36 @@ class ProjectsFinder
 
   def group_projects(current_user, group)
     if current_user
-      if group.users.include?(current_user)
-        # User is group member
-        #
-        # Return ALL group projects
-        group.projects
-      else
-        projects_members = ProjectMember.in_projects(group.projects).
-          with_user(current_user)
-
-        if projects_members.any?
-          # User is a project member
-          #
-          # Return only:
-          #   public projects
-          #   internal projects
-          #   joined projects
-          #
-          group.projects.where(
-            "projects.id IN (?) OR projects.visibility_level IN (?)",
-            projects_members.pluck(:source_id),
-            Project.public_and_internal_levels
-          )
-        else
-          # User has no access to group or group projects
-          #
-          # Return only:
-          #   public projects
-          #   internal projects
-          #
-          group.projects.public_and_internal_only
-        end
-      end
+      [
+        group_projects_for_user(current_user, group),
+        group.projects.public_and_internal_only
+      ]
     else
-      # Not authenticated
-      #
-      # Return only:
-      #   public projects
-      group.projects.public_only
+      [group.projects.public_only]
     end
   end
 
   def all_projects(current_user)
     if current_user
-      if current_user.authorized_projects.any?
-        # User has access to private projects
-        #
-        # Return only:
-        #   public projects
-        #   internal projects
-        #   joined projects
-        #
-        Project.where(
-          "projects.id IN (?) OR projects.visibility_level IN (?)",
-          current_user.authorized_projects.pluck(:id),
-          Project.public_and_internal_levels
-        )
-      else
-        # User has no access to private projects
-        #
-        # Return only:
-        #   public projects
-        #   internal projects
-        #
-        Project.public_and_internal_only
-      end
+      [current_user.authorized_projects, public_and_internal_projects]
     else
-      # Not authenticated
-      #
-      # Return only:
-      #   public projects
-      Project.public_only
+      [Project.public_only]
     end
   end
+
+  def group_projects_for_user(current_user, group)
+    if group.users.include?(current_user)
+      group.projects
+    else
+      group.projects.visible_to_user(current_user)
+    end
+  end
+
+  def public_projects
+    Project.unscoped.public_only
+  end
+
+  def public_and_internal_projects
+    Project.unscoped.public_and_internal_only
+  end
 end
diff --git a/app/models/concerns/sortable.rb b/app/models/concerns/sortable.rb
index 913c747a1c3..7391a77383c 100644
--- a/app/models/concerns/sortable.rb
+++ b/app/models/concerns/sortable.rb
@@ -8,8 +8,9 @@ module Sortable
   included do
     # By default all models should be ordered
     # by created_at field starting from newest
-    default_scope { order(id: :desc) }
+    default_scope { order_id_desc }
 
+    scope :order_id_desc, -> { reorder(id: :desc) }
     scope :order_created_desc, -> { reorder(created_at: :desc) }
     scope :order_created_asc, -> { reorder(created_at: :asc) }
     scope :order_updated_desc, -> { reorder(updated_at: :desc) }
diff --git a/app/models/event.rb b/app/models/event.rb
index bf64ac29d32..9afd223bce5 100644
--- a/app/models/event.rb
+++ b/app/models/event.rb
@@ -63,6 +63,16 @@ class Event < ActiveRecord::Base
             Event::PUSHED, ["MergeRequest", "Issue"],
             [Event::CREATED, Event::CLOSED, Event::MERGED])
     end
+
+    def latest_update_time
+      row = select(:updated_at, :project_id).reorder(id: :desc).take
+
+      row ? row.updated_at : nil
+    end
+
+    def limit_recent(limit = 20, offset = nil)
+      recent.limit(limit).offset(offset)
+    end
   end
 
   def proper?
diff --git a/app/models/group.rb b/app/models/group.rb
index 2c9e75496b9..1b5b875a19e 100644
--- a/app/models/group.rb
+++ b/app/models/group.rb
@@ -49,6 +49,14 @@ class Group < Namespace
     def reference_pattern
       User.reference_pattern
     end
+
+    def public_and_given_groups(ids)
+      where('public IS TRUE OR namespaces.id IN (?)', ids)
+    end
+
+    def visible_to_user(user)
+      where(id: user.authorized_groups.select(:id).reorder(nil))
+    end
   end
 
   def to_reference(_from_project = nil)
diff --git a/app/models/project.rb b/app/models/project.rb
index 70a648e68a3..f0a4b6aae7b 100644
--- a/app/models/project.rb
+++ b/app/models/project.rb
@@ -286,6 +286,10 @@ class Project < ActiveRecord::Base
 
       joins(join_body).reorder('join_note_counts.amount DESC')
     end
+
+    def visible_to_user(user)
+      where(id: user.authorized_projects.select(:id).reorder(nil))
+    end
   end
 
   def team
diff --git a/app/models/user.rb b/app/models/user.rb
index 61abea1f6ea..9374f01f99f 100644
--- a/app/models/user.rb
+++ b/app/models/user.rb
@@ -389,42 +389,23 @@ class User < ActiveRecord::Base
     end
   end
 
-  # Groups user has access to
+  # Returns the groups a user has access to
   def authorized_groups
-    @authorized_groups ||= begin
-                             group_ids = (groups.pluck(:id) + authorized_projects.pluck(:namespace_id))
-                             Group.where(id: group_ids)
-                           end
-  end
+    union = Gitlab::SQL::Union.
+      new([groups.select(:id), authorized_projects.select(:namespace_id)])
 
-  def authorized_projects_id
-    @authorized_projects_id ||= begin
-      project_ids = personal_projects.pluck(:id)
-      project_ids.push(*groups_projects.pluck(:id))
-      project_ids.push(*projects.pluck(:id).uniq)
-    end
-  end
-
-  def master_or_owner_projects_id
-    @master_or_owner_projects_id ||= begin
-      scope = { access_level: [ Gitlab::Access::MASTER, Gitlab::Access::OWNER ] }
-      project_ids = personal_projects.pluck(:id)
-      project_ids.push(*groups_projects.where(members: scope).pluck(:id))
-      project_ids.push(*projects.where(members: scope).pluck(:id).uniq)
-    end
+    Group.where("namespaces.id IN (#{union.to_sql})")
   end
 
-  # Projects user has access to
+  # Returns the groups a user is authorized to access.
   def authorized_projects
-    @authorized_projects ||= Project.where(id: authorized_projects_id)
+    Project.where("projects.id IN (#{projects_union.to_sql})")
   end
 
   def owned_projects
     @owned_projects ||=
-      begin
-        namespace_ids = owned_groups.pluck(:id).push(namespace.id)
-        Project.in_namespace(namespace_ids).joins(:namespace)
-      end
+      Project.where('namespace_id IN (?) OR namespace_id = ?',
+                    owned_groups.select(:id), namespace.id).joins(:namespace)
   end
 
   # Team membership in authorized projects
@@ -739,12 +720,25 @@ class User < ActiveRecord::Base
     Doorkeeper::AccessToken.where(resource_owner_id: self.id, revoked_at: nil)
   end
 
-  def contributed_projects_ids
-    Event.contributions.where(author_id: self).
+  # Returns the projects a user contributed to in the last year.
+  #
+  # This method relies on a subquery as this performs significantly better
+  # compared to a JOIN when coupled with, for example,
+  # `Project.visible_to_user`. That is, consider the following code:
+  #
+  #     some_user.contributed_projects.visible_to_user(other_user)
+  #
+  # If this method were to use a JOIN the resulting query would take roughly 200
+  # ms on a database with a similar size to GitLab.com's database. On the other
+  # hand, using a subquery means we can get the exact same data in about 40 ms.
+  def contributed_projects
+    events = Event.select(:project_id).
+      contributions.where(author_id: self).
       where("created_at > ?", Time.now - 1.year).
-      reorder(project_id: :desc).
-      select(:project_id).
-      uniq.map(&:project_id)
+      uniq.
+      reorder(nil)
+
+    Project.where(id: events)
   end
 
   def restricted_signup_domains
@@ -777,8 +771,27 @@ class User < ActiveRecord::Base
   def ci_authorized_runners
     @ci_authorized_runners ||= begin
       runner_ids = Ci::RunnerProject.joins(:project).
-        where(ci_projects: { gitlab_id: master_or_owner_projects_id }).select(:runner_id)
+        where("ci_projects.gitlab_id IN (#{ci_projects_union.to_sql})").
+        select(:runner_id)
+
       Ci::Runner.specific.where(id: runner_ids)
     end
   end
+
+  private
+
+  def projects_union
+    Gitlab::SQL::Union.new([personal_projects.select(:id),
+                            groups_projects.select(:id),
+                            projects.select(:id)])
+  end
+
+  def ci_projects_union
+    scope  = { access_level: [Gitlab::Access::MASTER, Gitlab::Access::OWNER] }
+    groups = groups_projects.where(members: scope)
+    other  = projects.where(members: scope)
+
+    Gitlab::SQL::Union.new([personal_projects.select(:id), groups.select(:id),
+                            other.select(:id)])
+  end
 end
diff --git a/app/views/dashboard/projects/index.atom.builder b/app/views/dashboard/projects/index.atom.builder
index d2c51486841..c8c219f4cca 100644
--- a/app/views/dashboard/projects/index.atom.builder
+++ b/app/views/dashboard/projects/index.atom.builder
@@ -4,7 +4,7 @@ xml.feed "xmlns" => "http://www.w3.org/2005/Atom", "xmlns:media" => "http://sear
   xml.link    href: dashboard_projects_url(format: :atom, private_token: current_user.try(:private_token)), rel: "self", type: "application/atom+xml"
   xml.link    href: dashboard_projects_url, rel: "alternate", type: "text/html"
   xml.id      dashboard_projects_url
-  xml.updated @events.maximum(:updated_at).strftime("%Y-%m-%dT%H:%M:%SZ") if @events.any?
+  xml.updated @events.latest_update_time.strftime("%Y-%m-%dT%H:%M:%SZ") if @events.any?
 
   @events.each do |event|
     event_to_atom(xml, event)
diff --git a/app/views/groups/show.atom.builder b/app/views/groups/show.atom.builder
index a91d1a6e94b..7ea574434c3 100644
--- a/app/views/groups/show.atom.builder
+++ b/app/views/groups/show.atom.builder
@@ -4,7 +4,7 @@ xml.feed "xmlns" => "http://www.w3.org/2005/Atom", "xmlns:media" => "http://sear
   xml.link    href: group_url(@group, format: :atom, private_token: current_user.try(:private_token)), rel: "self", type: "application/atom+xml"
   xml.link    href: group_url(@group), rel: "alternate", type: "text/html"
   xml.id      group_url(@group)
-  xml.updated @events.maximum(:updated_at).strftime("%Y-%m-%dT%H:%M:%SZ") if @events.any?
+  xml.updated @events.latest_update_time.strftime("%Y-%m-%dT%H:%M:%SZ") if @events.any?
 
   @events.each do |event|
     event_to_atom(xml, event)
diff --git a/app/views/projects/show.atom.builder b/app/views/projects/show.atom.builder
index 242684e5c7c..15c49767556 100644
--- a/app/views/projects/show.atom.builder
+++ b/app/views/projects/show.atom.builder
@@ -4,7 +4,7 @@ xml.feed "xmlns" => "http://www.w3.org/2005/Atom", "xmlns:media" => "http://sear
   xml.link    href: namespace_project_url(@project.namespace, @project, format: :atom, private_token: current_user.try(:private_token)), rel: "self", type: "application/atom+xml"
   xml.link    href: namespace_project_url(@project.namespace, @project), rel: "alternate", type: "text/html"
   xml.id      namespace_project_url(@project.namespace, @project)
-  xml.updated @events.maximum(:updated_at).strftime("%Y-%m-%dT%H:%M:%SZ") if @events.any?
+  xml.updated @events.latest_update_time.strftime("%Y-%m-%dT%H:%M:%SZ") if @events.any?
 
   @events.each do |event|
     event_to_atom(xml, event)
diff --git a/app/views/users/show.atom.builder b/app/views/users/show.atom.builder
index 50232dc7186..2fe5b7fac83 100644
--- a/app/views/users/show.atom.builder
+++ b/app/views/users/show.atom.builder
@@ -4,7 +4,7 @@ xml.feed "xmlns" => "http://www.w3.org/2005/Atom", "xmlns:media" => "http://sear
   xml.link    href: user_url(@user, :atom), rel: "self", type: "application/atom+xml"
   xml.link    href: user_url(@user), rel: "alternate", type: "text/html"
   xml.id      user_url(@user)
-  xml.updated @events.maximum(:updated_at).strftime("%Y-%m-%dT%H:%M:%SZ") if @events.any?
+  xml.updated @events.latest_update_time.strftime("%Y-%m-%dT%H:%M:%SZ") if @events.any?
 
   @events.each do |event|
     event_to_atom(xml, event)
diff --git a/db/migrate/20151118162244_add_projects_public_index.rb b/db/migrate/20151118162244_add_projects_public_index.rb
new file mode 100644
index 00000000000..fded70e3c0c
--- /dev/null
+++ b/db/migrate/20151118162244_add_projects_public_index.rb
@@ -0,0 +1,5 @@
+class AddProjectsPublicIndex < ActiveRecord::Migration
+  def change
+    add_index :namespaces, :public
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index f77f6dfc66d..80e9405f6f1 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -11,7 +11,7 @@
 #
 # It's strongly recommended that you check this file into your version control system.
 
-ActiveRecord::Schema.define(version: 20151116144118) do
+ActiveRecord::Schema.define(version: 20151118162244) do
 
   # These are extensions that must be enabled in order to support this database
   enable_extension "plpgsql"
@@ -538,6 +538,7 @@ ActiveRecord::Schema.define(version: 20151116144118) do
   add_index "namespaces", ["name"], name: "index_namespaces_on_name", unique: true, using: :btree
   add_index "namespaces", ["owner_id"], name: "index_namespaces_on_owner_id", using: :btree
   add_index "namespaces", ["path"], name: "index_namespaces_on_path", unique: true, using: :btree
+  add_index "namespaces", ["public"], name: "index_namespaces_on_public", using: :btree
   add_index "namespaces", ["type"], name: "index_namespaces_on_type", using: :btree
 
   create_table "notes", force: true do |t|
diff --git a/lib/gitlab/sql/union.rb b/lib/gitlab/sql/union.rb
new file mode 100644
index 00000000000..1cd89b3a9c4
--- /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("\nUNION\n")
+      end
+    end
+  end
+end
diff --git a/spec/controllers/users_controller_spec.rb b/spec/controllers/users_controller_spec.rb
index 9f89101d7f7..104a5f50143 100644
--- a/spec/controllers/users_controller_spec.rb
+++ b/spec/controllers/users_controller_spec.rb
@@ -16,13 +16,26 @@ describe UsersController do
     context 'with rendered views' do
       render_views
 
-      it 'renders the show template' do
-        sign_in(user)
+      describe 'when logged in' do
+        before do
+          sign_in(user)
+        end
 
-        get :show, username: user.username
+        it 'renders the show template' do
+          get :show, username: user.username
 
-        expect(response).to be_success
-        expect(response).to render_template('show')
+          expect(response).to be_success
+          expect(response).to render_template('show')
+        end
+      end
+
+      describe 'when logged out' do
+        it 'renders the show template' do
+          get :show, username: user.username
+
+          expect(response).to be_success
+          expect(response).to render_template('show')
+        end
       end
     end
   end
diff --git a/spec/finders/contributed_projects_finder_spec.rb b/spec/finders/contributed_projects_finder_spec.rb
new file mode 100644
index 00000000000..65d7f14c721
--- /dev/null
+++ b/spec/finders/contributed_projects_finder_spec.rb
@@ -0,0 +1,35 @@
+require 'spec_helper'
+
+describe ContributedProjectsFinder do
+  let(:source_user) { create(:user) }
+  let(:current_user) { create(:user) }
+
+  let(:finder) { described_class.new(source_user) }
+
+  let!(:public_project) { create(:project, :public) }
+  let!(:private_project) { create(:project, :private) }
+
+  before do
+    private_project.team << [source_user, Gitlab::Access::MASTER]
+    private_project.team << [current_user, Gitlab::Access::DEVELOPER]
+    public_project.team << [source_user, Gitlab::Access::MASTER]
+
+    create(:event, action: Event::PUSHED, project: public_project,
+                   target: public_project, author: source_user)
+
+    create(:event, action: Event::PUSHED, project: private_project,
+                   target: private_project, author: source_user)
+  end
+
+  describe 'without a current user' do
+    subject { finder.execute }
+
+    it { is_expected.to eq([public_project]) }
+  end
+
+  describe 'with a current user' do
+    subject { finder.execute(current_user) }
+
+    it { is_expected.to eq([private_project, public_project]) }
+  end
+end
diff --git a/spec/finders/group_finder_spec.rb b/spec/finders/group_finder_spec.rb
deleted file mode 100644
index 78dc027837c..00000000000
--- a/spec/finders/group_finder_spec.rb
+++ /dev/null
@@ -1,15 +0,0 @@
-require 'spec_helper'
-
-describe GroupsFinder do
-  let(:user) { create :user }
-  let!(:group) { create :group }
-  let!(:public_group) { create :group, public: true }
-  
-  describe :execute do
-    it 'finds public group' do
-      groups = GroupsFinder.new.execute(user)
-      expect(groups.size).to eq(1)
-      expect(groups.first).to eq(public_group)
-    end
-  end
-end
diff --git a/spec/finders/groups_finder_spec.rb b/spec/finders/groups_finder_spec.rb
new file mode 100644
index 00000000000..4f6a000822e
--- /dev/null
+++ b/spec/finders/groups_finder_spec.rb
@@ -0,0 +1,48 @@
+require 'spec_helper'
+
+describe GroupsFinder do
+  describe '#execute' do
+    let(:user) { create(:user) }
+
+    let(:group1) { create(:group) }
+    let(:group2) { create(:group) }
+    let(:group3) { create(:group) }
+    let(:group4) { create(:group, public: true) }
+
+    let!(:public_project)   { create(:project, :public, group: group1) }
+    let!(:internal_project) { create(:project, :internal, group: group2) }
+    let!(:private_project)  { create(:project, :private, group: group3) }
+
+    let(:finder) { described_class.new }
+
+    describe 'with a user' do
+      subject { finder.execute(user) }
+
+      describe 'when the user is not a member of any groups' do
+        it { is_expected.to eq([group4, group2, group1]) }
+      end
+
+      describe 'when the user is a member of a group' do
+        before do
+          group3.add_user(user, Gitlab::Access::DEVELOPER)
+        end
+
+        it { is_expected.to eq([group4, group3, group2, group1]) }
+      end
+
+      describe 'when the user is a member of a private project' do
+        before do
+          private_project.team.add_user(user, Gitlab::Access::DEVELOPER)
+        end
+
+        it { is_expected.to eq([group4, group3, group2, group1]) }
+      end
+    end
+
+    describe 'without a user' do
+      subject { finder.execute }
+
+      it { is_expected.to eq([group4, group1]) }
+    end
+  end
+end
diff --git a/spec/finders/joined_groups_finder_spec.rb b/spec/finders/joined_groups_finder_spec.rb
new file mode 100644
index 00000000000..2d9068cc720
--- /dev/null
+++ b/spec/finders/joined_groups_finder_spec.rb
@@ -0,0 +1,49 @@
+require 'spec_helper'
+
+describe JoinedGroupsFinder do
+  describe '#execute' do
+    let(:source_user) { create(:user) }
+    let(:current_user) { create(:user) }
+
+    let(:group1) { create(:group) }
+    let(:group2) { create(:group) }
+    let(:group3) { create(:group) }
+    let(:group4) { create(:group, public: true) }
+
+    let!(:public_project)   { create(:project, :public, group: group1) }
+    let!(:internal_project) { create(:project, :internal, group: group2) }
+    let!(:private_project)  { create(:project, :private, group: group3) }
+
+    let(:finder) { described_class.new(source_user) }
+
+    before do
+      [group1, group2, group3, group4].each do |group|
+        group.add_user(source_user, Gitlab::Access::MASTER)
+      end
+    end
+
+    describe 'with a current user' do
+      describe 'when the current user has access to the projects of the source user' do
+        before do
+          private_project.team.add_user(current_user, Gitlab::Access::DEVELOPER)
+        end
+
+        subject { finder.execute(current_user) }
+
+        it { is_expected.to eq([group4, group3, group2, group1]) }
+      end
+
+      describe 'when the current user does not have access to the projects of the source user' do
+        subject { finder.execute(current_user) }
+
+        it { is_expected.to eq([group4, group2, group1]) }
+      end
+    end
+
+    describe 'without a current user' do
+      subject { finder.execute }
+
+      it { is_expected.to eq([group4, group1]) }
+    end
+  end
+end
diff --git a/spec/finders/personal_projects_finder_spec.rb b/spec/finders/personal_projects_finder_spec.rb
new file mode 100644
index 00000000000..38817add456
--- /dev/null
+++ b/spec/finders/personal_projects_finder_spec.rb
@@ -0,0 +1,34 @@
+require 'spec_helper'
+
+describe PersonalProjectsFinder do
+  let(:source_user) { create(:user) }
+  let(:current_user) { create(:user) }
+
+  let(:finder) { described_class.new(source_user) }
+
+  let!(:public_project) do
+    create(:project, :public, namespace: source_user.namespace, name: 'A',
+                              path: 'A')
+  end
+
+  let!(:private_project) do
+    create(:project, :private, namespace: source_user.namespace, name: 'B',
+                               path: 'B')
+  end
+
+  before do
+    private_project.team << [current_user, Gitlab::Access::DEVELOPER]
+  end
+
+  describe 'without a current user' do
+    subject { finder.execute }
+
+    it { is_expected.to eq([public_project]) }
+  end
+
+  describe 'with a current user' do
+    subject { finder.execute(current_user) }
+
+    it { is_expected.to eq([private_project, public_project]) }
+  end
+end
diff --git a/spec/finders/projects_finder_spec.rb b/spec/finders/projects_finder_spec.rb
index de9d4cd6128..d1dede78f74 100644
--- a/spec/finders/projects_finder_spec.rb
+++ b/spec/finders/projects_finder_spec.rb
@@ -1,51 +1,56 @@
 require 'spec_helper'
 
 describe ProjectsFinder do
-  let(:user) { create :user }
-  let(:group) { create :group }
+  describe '#execute' do
+    let(:user) { create(:user) }
 
-  let(:project1) { create(:empty_project, :public,   group: group) }
-  let(:project2) { create(:empty_project, :internal, group: group) }
-  let(:project3) { create(:empty_project, :private,  group: group) }
-  let(:project4) { create(:empty_project, :private,  group: group) }
+    let!(:private_project)  { create(:project, :private) }
+    let!(:internal_project) { create(:project, :internal) }
+    let!(:public_project)   { create(:project, :public) }
 
-  context 'non authenticated' do
-    subject { ProjectsFinder.new.execute(nil, group: group) }
+    let(:finder) { described_class.new }
 
-    it { is_expected.to include(project1) }
-    it { is_expected.not_to include(project2) }
-    it { is_expected.not_to include(project3) }
-    it { is_expected.not_to include(project4) }
-  end
+    describe 'without a group' do
+      describe 'without a user' do
+        subject { finder.execute }
 
-  context 'authenticated' do
-    subject { ProjectsFinder.new.execute(user, group: group) }
+        it { is_expected.to eq([public_project]) }
+      end
 
-    it { is_expected.to include(project1) }
-    it { is_expected.to include(project2) }
-    it { is_expected.not_to include(project3) }
-    it { is_expected.not_to include(project4) }
-  end
+      describe 'with a user' do
+        subject { finder.execute(user) }
 
-  context 'authenticated, project member' do
-    before { project3.team << [user, :developer] }
+        describe 'without private projects' do
+          it { is_expected.to eq([public_project, internal_project]) }
+        end
 
-    subject { ProjectsFinder.new.execute(user, group: group) }
+        describe 'with private projects' do
+          before do
+            private_project.team.add_user(user, Gitlab::Access::MASTER)
+          end
 
-    it { is_expected.to include(project1) }
-    it { is_expected.to include(project2) }
-    it { is_expected.to include(project3) }
-    it { is_expected.not_to include(project4) }
-  end
+          it do
+            is_expected.to eq([public_project, internal_project,
+                               private_project])
+          end
+        end
+      end
+    end
+
+    describe 'with a group' do
+      let(:group) { public_project.group }
+
+      describe 'without a user' do
+        subject { finder.execute(nil, group: group) }
 
-  context 'authenticated, group member' do
-    before { group.add_developer(user) }
+        it { is_expected.to eq([public_project]) }
+      end
 
-    subject { ProjectsFinder.new.execute(user, group: group) }
+      describe 'with a user' do
+        subject { finder.execute(user, group: group) }
 
-    it { is_expected.to include(project1) }
-    it { is_expected.to include(project2) }
-    it { is_expected.to include(project3) }
-    it { is_expected.to include(project4) }
+        it { is_expected.to eq([public_project, internal_project]) }
+      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..9e1cd4419e0
--- /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}\nUNION\n#{sql2}")
+    end
+  end
+end
diff --git a/spec/models/event_spec.rb b/spec/models/event_spec.rb
index 0f32f162a10..ae53f7a536b 100644
--- a/spec/models/event_spec.rb
+++ b/spec/models/event_spec.rb
@@ -64,4 +64,42 @@ describe Event do
     it { expect(@event.branch_name).to eq("master") }
     it { expect(@event.author).to eq(@user) }
   end
+
+  describe '.latest_update_time' do
+    describe 'when events are present' do
+      let(:time) { Time.utc(2015, 1, 1) }
+
+      before do
+        create(:closed_issue_event, updated_at: time)
+        create(:closed_issue_event, updated_at: time + 5)
+      end
+
+      it 'returns the latest update time' do
+        expect(Event.latest_update_time).to eq(time + 5)
+      end
+    end
+
+    describe 'when no events exist' do
+      it 'returns nil' do
+        expect(Event.latest_update_time).to be_nil
+      end
+    end
+  end
+
+  describe '.limit_recent' do
+    let!(:event1) { create(:closed_issue_event) }
+    let!(:event2) { create(:closed_issue_event) }
+
+    describe 'without an explicit limit' do
+      subject { Event.limit_recent }
+
+      it { is_expected.to eq([event2, event1]) }
+    end
+
+    describe 'with an explicit limit' do
+      subject { Event.limit_recent(1) }
+
+      it { is_expected.to eq([event2]) }
+    end
+  end
 end
diff --git a/spec/models/group_spec.rb b/spec/models/group_spec.rb
index bbfc5535eec..6f166b5ab75 100644
--- a/spec/models/group_spec.rb
+++ b/spec/models/group_spec.rb
@@ -38,6 +38,33 @@ describe Group do
     it { is_expected.not_to validate_presence_of :owner }
   end
 
+  describe '.public_and_given_groups' do
+    let!(:public_group) { create(:group, public: true) }
+
+    subject { described_class.public_and_given_groups([group.id]) }
+
+    it { is_expected.to eq([public_group, group]) }
+  end
+
+  describe '.visible_to_user' do
+    let!(:group) { create(:group) }
+    let!(:user)  { create(:user) }
+
+    subject { described_class.visible_to_user(user) }
+
+    describe 'when the user has access to a group' do
+      before do
+        group.add_user(user, Gitlab::Access::MASTER)
+      end
+
+      it { is_expected.to eq([group]) }
+    end
+
+    describe 'when the user does not have access to any groups' do
+      it { is_expected.to eq([]) }
+    end
+  end
+
   describe '#to_reference' do
     it 'returns a String reference to the object' do
       expect(group.to_reference).to eq "@#{group.name}"
diff --git a/spec/models/project_spec.rb b/spec/models/project_spec.rb
index 3c537220106..f80fada45e9 100644
--- a/spec/models/project_spec.rb
+++ b/spec/models/project_spec.rb
@@ -453,4 +453,23 @@ describe Project do
       end
     end
   end
+
+  describe '.visible_to_user' do
+    let!(:project) { create(:project, :private) }
+    let!(:user)    { create(:user) }
+
+    subject { described_class.visible_to_user(user) }
+
+    describe 'when a user has access to a project' do
+      before do
+        project.team.add_user(user, Gitlab::Access::MASTER)
+      end
+
+      it { is_expected.to eq([project]) }
+    end
+
+    describe 'when a user does not have access to any projects' do
+      it { is_expected.to eq([]) }
+    end
+  end
 end
diff --git a/spec/models/user_spec.rb b/spec/models/user_spec.rb
index 7d716c23120..4631b12faf1 100644
--- a/spec/models/user_spec.rb
+++ b/spec/models/user_spec.rb
@@ -686,7 +686,7 @@ describe User do
     end
   end
 
-  describe "#contributed_projects_ids" do
+  describe "#contributed_projects" do
     subject { create(:user) }
     let!(:project1) { create(:project) }
     let!(:project2) { create(:project, forked_from_project: project3) }
@@ -701,15 +701,15 @@ describe User do
     end
 
     it "includes IDs for projects the user has pushed to" do
-      expect(subject.contributed_projects_ids).to include(project1.id)
+      expect(subject.contributed_projects).to include(project1)
     end
 
     it "includes IDs for projects the user has had merge requests merged into" do
-      expect(subject.contributed_projects_ids).to include(project3.id)
+      expect(subject.contributed_projects).to include(project3)
     end
 
     it "doesn't include IDs for unrelated projects" do
-      expect(subject.contributed_projects_ids).not_to include(project2.id)
+      expect(subject.contributed_projects).not_to include(project2)
     end
   end
 
@@ -758,4 +758,30 @@ describe User do
       expect(subject.recent_push).to eq(nil)
     end
   end
+
+  describe '#authorized_groups' do
+    let!(:user) { create(:user) }
+    let!(:private_group) { create(:group) }
+
+    before do
+      private_group.add_user(user, Gitlab::Access::MASTER)
+    end
+
+    subject { user.authorized_groups }
+
+    it { is_expected.to eq([private_group]) }
+  end
+
+  describe '#authorized_projects' do
+    let!(:user) { create(:user) }
+    let!(:private_project) { create(:project, :private) }
+
+    before do
+      private_project.team << [user, Gitlab::Access::MASTER]
+    end
+
+    subject { user.authorized_projects }
+
+    it { is_expected.to eq([private_project]) }
+  end
 end