Query Overview

Queries for Harvest Report

1 Shrink the amount of data with a scope in class Visit < ActiveRecord::Base  

scope :harvest_visits, -> { where('visited_on >= ?', 3.months.ago )}
 
 All Records (Rails Console):

Visit.all.count
  Visit Load (408.3ms)  SELECT "visits".* FROM "visits" ORDER BY visited_on DESC
  EXPLAIN (5.0ms)  EXPLAIN SELECT "visits".* FROM "visits" ORDER BY visited_on DESC
EXPLAIN for: SELECT "visits".* FROM "visits"  ORDER BY visited_on DESC
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Index Scan Backward using index_visits_on_visited_on on visits  (cost=0.00..987.24 rows=24297 width=108)
(1 row)

=> 24294

Scoped Records (Rails Console):

Visit.harvest_visits.count
   (5.7ms)  SELECT COUNT(*) FROM "visits" WHERE (visited_on >= '2018-03-20 23:54:10.640658')
=> 1001

 Get the Month
 
 @month
=> "June 2018"

Query By Month does not Work?  Probably format of Month

 Visit.harvest_visits.by_month(@month).count
   (2.5ms)  SELECT COUNT(*) FROM "visits" WHERE (visited_on >= '2018-03-20 23:56:23.335690') AND (select(to_char(visited_on, 'FMMonth')) = 'June 2018')
=> 0

Moving on =>

Pull out the unique Households for the scope using includes for eager loading:

Visit.harvest_visits.includes(:housholds, :neighbors).pluck(:household_id).uniq
   (2.1ms)  SELECT household_id FROM "visits" WHERE (visited_on >= '2018-03-20 23:58:17.948679') ORDER BY visited_on DESC
=> [1478,
 1809,
 2013,
 2277,
 22,
 1444,
 66, . . . .
 
 Let's Count them:
 
 Visit.harvest_visits.includes(:housholds, :neighbors).pluck(:household_id).uniq.count
   (1.8ms)  SELECT household_id FROM "visits" WHERE (visited_on >= '2018-03-20 23:59:45.918543') ORDER BY visited_on DESC
=> 562

Assign them to a variable:

@hi = Visit.harvest_visits.includes(:housholds, :neighbors).pluck(:household_id).uniq

Count the Neighbors in the Households:

 @hi.map{|id| Household.find(id).neighbor_count}.inject(:+)
 .
 .
 .
  Household Load (0.5ms)  SELECT "households".* FROM "households" WHERE "households"."id" = $1 ORDER BY household_name ASC LIMIT 1  [["id", 2366]]
   (0.6ms)  SELECT COUNT(*) FROM "neighbors" WHERE "neighbors"."household_id" = 2366
  Household Load (0.4ms)  SELECT "households".* FROM "households" WHERE "households"."id" = $1 ORDER BY household_name ASC LIMIT 1  [["id", 1206]]
   (0.4ms)  SELECT COUNT(*) FROM "neighbors" WHERE "neighbors"."household_id" = 1206
  Household Load (0.4ms)  SELECT "households".* FROM "households" WHERE "households"."id" = $1 ORDER BY household_name ASC LIMIT 1  [["id", 3113]]
   (0.4ms)  SELECT COUNT(*) FROM "neighbors" WHERE "neighbors"."household_id" = 3113
  Household Load (0.4ms)  SELECT "households".* FROM "households" WHERE "households"."id" = $1 ORDER BY household_name ASC LIMIT 1  [["id", 1458]]
   (0.4ms)  SELECT COUNT(*) FROM "neighbors" WHERE "neighbors"."household_id" = 1458
=> 1553

562 * .04 = 22.48   Better than 10 minutes but not Great

To put this in Active Record I tried:


  def self.visits_month
    @month = "#{Date.today.strftime('%B %Y')}"
    Visit.harvest_visits.by_month(@month).count
  end
 
 Which is wrong -- But it is a class method? 
 
 But I am thinking each Query above is a class method?