From 353b170392892a1f9bdde74550112b5131c99fd2 Mon Sep 17 00:00:00 2001
From: Kelly Rauchenberger <fefferburbia@gmail.com>
Date: Tue, 3 Oct 2017 22:49:19 -0400
Subject: Optimized front page queries
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Reduced the number of queries that the front page runs to 2 queries
total: one to get trainer data, and one to get the Pokémon. The latter
query selects only the necessary columns, notably ignoring basically
everything from the revisions join other than the nickname of the most
recent revision. The controller then does the work of chunking the
Pokémon into party/boxes, and then into trainers/unaccounted.

refs #2
---
 app/controllers/pokeviewer/pokemon_controller.rb | 67 ++++++++++++++++++++++--
 app/views/pokeviewer/pokemon/index.html.haml     | 12 ++---
 2 files changed, 70 insertions(+), 9 deletions(-)

(limited to 'app')

diff --git a/app/controllers/pokeviewer/pokemon_controller.rb b/app/controllers/pokeviewer/pokemon_controller.rb
index 17bd83d..f9f6c15 100644
--- a/app/controllers/pokeviewer/pokemon_controller.rb
+++ b/app/controllers/pokeviewer/pokemon_controller.rb
@@ -3,12 +3,73 @@ require_dependency "pokeviewer/application_controller"
 module Pokeviewer
   class PokemonController < ApplicationController
     def index
-      @trainers = Trainer.all
-      @unaccounted = Pokemon.where(trainer: nil)
+      pokemon = Pokemon.joins(:revisions).
+        order("trainer_id IS NULL DESC").
+        order(trainer_id: :asc).
+        order(box: :asc).
+        order(slot: :asc).
+        order("pokeviewer_revisions.sequential_id DESC").
+        group("pokeviewer_pokemon.uuid").
+        select(:box, :slot, :uuid, :trainer_id, :species_id).
+        select(:ot_gender, :ot_name, :unown_letter).
+        select("pokeviewer_revisions.nickname AS nickname").
+        chunk do |p|
+          if p.trainer_id.nil?
+            -1
+          else
+            p.trainer_id
+          end
+        end
+
+      if pokemon.peek.first == -1
+        @unaccounted = pokemon.next.second
+      else
+        @unaccounted = []
+      end
+
+      @trainers = Trainer.order(id: :asc).all.map do |trainer|
+        if trainer.id == pokemon.peek.first
+          party = []
+
+          boxes = (1..14).map do |i|
+            {
+              name: trainer.box_name(i),
+              pokemon: [nil] * 30
+            }
+          end
+
+          pokemon.next.second.chunk do |p|
+            if p.box.nil?
+              -1
+            else
+              p.box
+            end
+          end.each do |box, pokes|
+            if box == -1
+              party = pokes
+            else
+              boxes[box-1][:pokemon] = (0..29).map do |i|
+                if not pokes.empty? and (pokes.first.slot == i)
+                  pokes.shift
+                else
+                  nil
+                end
+              end
+            end
+          end
+
+          [trainer, party, boxes]
+        else
+          nil
+        end
+      end.compact
     end
 
     def show
-      @pokemon = Pokemon.find_by_uuid! params[:id]
+      @pokemon = Pokemon.includes(
+          :trainer, :species, :location,
+          revisions: [:item, :move_1, :move_2, :move_3, :move_4]
+        ).find_by_uuid! params[:id]
     end
   end
 end
diff --git a/app/views/pokeviewer/pokemon/index.html.haml b/app/views/pokeviewer/pokemon/index.html.haml
index 0be3edd..e1793dc 100644
--- a/app/views/pokeviewer/pokemon/index.html.haml
+++ b/app/views/pokeviewer/pokemon/index.html.haml
@@ -1,4 +1,4 @@
-- @trainers.each do |trainer|
+- @trainers.each do |trainer, party, boxes|
   .trainer
     .trainer-info{ class: trainer.game }
       %h2= trainer.name
@@ -6,11 +6,11 @@
     .pc-boxes
       %ul.party.pc-box
         %h3 Party
-        - trainer.party.each do |p|
+        - party.each do |p|
           %li
             %span.party-icon= image_tag p.icon_path
-            %span.party-name= link_to p.revisions.last.nickname, p
-      - trainer.boxes.each do |box|
+            %span.party-name= link_to p.nickname, p
+      - boxes.each do |box|
         .pc-box
           %h3= box[:name]
           %table.pc-contents
@@ -23,7 +23,7 @@
                     - else
                       = link_to image_tag(p.icon_path), p
                       .pc-data.pkv-hover
-                        .pc-data-name= p.revisions.last.nickname
+                        .pc-data-name= p.nickname
                         .pc-data-ot
                           OT/
                           %span{ class: p.ot_gender }>= p.ot_name
@@ -36,7 +36,7 @@
         %li.pc-pokemon.pkv-has-hover
           = link_to image_tag(p.icon_path), p
           .pc-data.pkv-hover
-            .pc-data-name= p.revisions.last.nickname
+            .pc-data-name= p.nickname
             .pc-data-ot
               OT/
               %span{ class: p.ot_gender }>= p.ot_name
-- 
cgit 1.4.1