Cached a Pokémon's latest revision

This removes the need to use a GROUP or DISTINCT in eager-loading Pokémon on the front page. It duplicates data, but provisions are in place to keep the cache up-to-date. When a Pokémon record is updated, it is required that its cache points to the current revision. When a revision is created, it updates the Pokémon's cache to point to it, because a new revision will always be the most recent one, and it is impossible to reorder revisions.

This does not affect the number of queries used on the front page (see #1).

refs #4

5 files changed, 47 insertions, 4 deletions

@@ -7,8 +7,7 @@ module Pokeviewer
         order(trainer_id: :asc).
         order(box: :asc).
         order(slot: :asc).
-        select(:uuid).distinct.
-        order("pokeviewer_revisions.sequential_id DESC").
+        joins(:current).
         includes(:current).
         chunk do |p|
           if p.trainer_id.nil?
@@ -4,7 +4,9 @@ module Pokeviewer
     extend ActiveModel::Naming
 
     has_many :revisions, -> { order "sequential_id ASC" }, dependent: :destroy
-    has_one :current, -> { order "sequential_id DESC" }, class_name: "Revision"
+
+    belongs_to :current, class_name: "Revision", optional: true
+    validate :current_is_cached
 
     belongs_to :trainer, optional: true
 
@@ -149,5 +151,17 @@ module Pokeviewer
       def uuid_is_constant
         errors.add(:uuid, "can't be changed") if self.uuid_changed?
       end
+
+      def current_is_cached
+        if self.revisions.empty?
+          unless self.current_id.nil?
+            errors.add(:current, "must be null when there are no revisions")
+          end
+        else
+          unless self.current_id = self.revisions.last.id
+            errors.add(:current, "is not up-to-date")
+          end
+        end
+      end
   end
 end
@@ -17,6 +17,8 @@ module Pokeviewer
     belongs_to :pokemon
     acts_as_sequenced scope: :pokemon_id
 
+    after_create :cache_pokemon_current
+
     belongs_to :species
 
     validates :nickname, presence: true
@@ -483,5 +485,12 @@ module Pokeviewer
 
       result
     end
+
+    private
+
+      def cache_pokemon_current
+        self.pokemon.current_id = self.id
+        self.pokemon.save!
+      end
   end
 end
@@ -0,0 +1,19 @@
+class CacheCurrentPokemonRevision < ActiveRecord::Migration[5.1]
+  def up
+    change_table :pokeviewer_pokemon do |t|
+      t.references :current, null: true
+    end
+
+    add_foreign_key :pokeviewer_pokemon, :pokeviewer_revisions,
+      column: :current_id
+
+    Pokeviewer::Pokemon.all.each do |p|
+      p.current_id = p.revisions.last.id
+      p.save!
+    end
+  end
+
+  def down
+    remove_column :pokeviewer_pokemon, :current_id
+  end
+end
@@ -10,7 +10,7 @@
 #
 # It's strongly recommended that you check this file into your version control system.
 
-ActiveRecord::Schema.define(version: 20180113200119) do
+ActiveRecord::Schema.define(version: 20180114170238) do
 
   create_table "pokeviewer_abilities", force: :cascade do |t|
     t.string "name", limit: 191, null: false
@@ -76,6 +76,8 @@ ActiveRecord::Schema.define(version: 20180113200119) do
     t.integer "slot"
     t.integer "location_id"
     t.string "pokeball", null: false
+    t.integer "current_id"
+    t.index ["current_id"], name: "index_pokeviewer_pokemon_on_current_id"
     t.index ["key"], name: "index_pokeviewer_pokemon_on_key", unique: true
     t.index ["trainer_id"], name: "index_pokeviewer_pokemon_on_trainer_id"
     t.index ["uuid"], name: "index_pokeviewer_pokemon_on_uuid", unique: true