Fixing N+1 queries in Rails that need a counter.

Question

I'm pretty new to optimizing my queries, I have an N+1 query and it seems it needs a counter, but I'm not really sure how to proceed:

...
SQL (0.5ms)  SELECT COUNT(*) AS count_id FROM (SELECT 1 FROM `photos` WHERE (`photos`.attachable_id = 4864 AND `photos`.attachable_type = 'Recipe')) AS subquery
SQL (2.1ms)  SELECT COUNT(*) AS count_id FROM (SELECT 1 FROM `votes` WHERE (`votes`.voteable_id = 4864 AND `votes`.voteable_type = 'Recipe') AND (`votes`.`vote` = 1)) AS subquery
SQL (2.0ms)  SELECT COUNT(*) AS count_id FROM (SELECT 1 FROM `votes` WHERE (`votes`.voteable_id = 4864 AND `votes`.voteable_type = 'Recipe') AND (`votes`.`vote` = 0)) AS subquery
SQL (0.3ms)  SELECT COUNT(*) AS count_id FROM (SELECT 1 FROM `photos` WHERE (`photos`.attachable_id = 4865 AND `photos`.attachable_type = 'Recipe')) AS subquery
SQL (2.6ms)  SELECT COUNT(*) AS count_id FROM (SELECT 1 FROM `votes` WHERE (`votes`.voteable_id = 4865 AND `votes`.voteable_type = 'Recipe') AND (`votes`.`vote` = 1)) AS subquery
SQL (2.4ms)  SELECT COUNT(*) AS count_id FROM (SELECT 1 FROM `votes` WHERE (`votes`.voteable_id = 4865 AND `votes`.voteable_type = 'Recipe') AND (`votes`.`vote` = 0)) AS subquery
...

So I have two odd things that pose a problem here. I have this polymorphic vote model, and I need to sum up all them, and basically if someone had casted a down vote it has value of 0 on the vote column, and a 1 if the users had cast an up vote. However it looks like It creates this insane query.

How could I remedy this?

---

I think the thing that is causing this starts with this method in my view:

<%= recipe.votes.tally %>

And this is the way I'm tallying my votes:

class Vote < ActiveRecord::Base

  scope :up, where(:vote => true)
  scope :down, where(:vote => false)

  def self.tally
    self.up.count - self.down.count
  end

end

But since I'm doing it for each Object it has to do this logic many times, we end out with a crazy N+1.

Answer 1

When you write,

I think the thing that is causing this starts with this method in my view:

< % = recipe.votes.tally % >

you can find more certainty by getting yourself a recipe instance in script/console and executing recipe.votes.tally there

Not quite sure, and this is a bit vague, but is it possible you should look at placing your tally method in the recipe model? -- Stephan