Rails: best way to turn these requests into a counter_cache

Question

I've got several of these requests each page load:

SELECT COUNT(*) FROM `impressions` WHERE `impressions`.`impressionable_id` IN (229) AND `impressions`.`impressionable_type` = 'Document' AND (YEAR(created_at) = 2013 && MONTH(created_at) = 8 && DAY(created_at) = 12)
SELECT COUNT(*) FROM `impressions` WHERE `impressions`.`impressionable_id` IN (229) AND `impressions`.`impressionable_type` = 'Document' AND (YEAR(created_at) = 2013 && MONTH(created_at) = 8 && DAY(created_at) = 13)
SELECT COUNT(*) FROM `impressions` WHERE `impressions`.`impressionable_id` IN (229) AND `impressions`.`impressionable_type` = 'Document' AND (YEAR(created_at) = 2013 && MONTH(created_at) = 8 && DAY(created_at) = 14)
SELECT COUNT(*) FROM `impressions` WHERE `impressions`.`impressionable_id` IN (229) AND `impressions`.`impressionable_type` = 'Document' AND (YEAR(created_at) = 2013 && MONTH(created_at) = 8 && DAY(created_at) = 15)

I'd like to reduce load on the server so I'm trying to turn it into a counter_cache solution. I need a bit of help designing it because of the complex relationship I'm trying to count. Here's the query that gets the counts:

Impression.where(:impressionable_id => component.publications.where(:document_id => document).first.id).count

Where should I put the counter_cache column? And how should I write the migration? Component and Document are in a has_many relationship through Publications.

Ivan Foong · Accepted Answer · 2013-08-26T04:34:11.930

1

Assuming impression counts are for Publication, and impression count is to be grouped by each day.

You could create PublicationImpression model to serve as a counter for the impression of each Publication per day.

class CreatePublicationImpressions < ActiveRecord::Migration
  def self.up
    create_table :publication_impressions, :id => false do |t|
      t.integer :impressions, :default => 0
      t.datetime :date
      t.references :publication

      t.timestamps
    end
  end

  def self.down
    drop_table :publication_impressions
  end
end

edited Aug 26 '13 at 04:34

answered Aug 26 '13 at 04:03

Ivan Foong

209
2
5

That's a good idea. I don't know if it was clear in my question, though, but I'm counting `Impressions` based on **both** `Component` and `Document`. I'm trying to figure out how best to implement that. – t56k Aug 26 '13 at 04:10
1

add the counter field to publication? I'm assuming `Publication` is unique for each pair of `Component` and `Document` – Ivan Foong Aug 26 '13 at 04:21
Yeah, `Publication` is unique. It's the join table between `Component` and `Document`. I was hoping to use that one instead of creating a similar table. – t56k Aug 26 '13 at 04:25
You would still need to create a new table for counter as the counts should be grouped by per day basis too. I have updated my answer to reflect that change. – Ivan Foong Aug 26 '13 at 04:33

Rails: best way to turn these requests into a counter_cache

1 Answers1