Optimisation de la tabulation quotidienne du classement

Question

Chaque poème a deux votes, un comme poem_id, other_poem_id, wins & le second enregistrement qui est l'inverse du premier. Il existe peut-être une meilleure façon de procéder, mais j'essaie de trouver les poèmes ayant le pourcentage de victoire le plus élevé sur une période donnée. C'est confus à cause des doubles enregistrements pour chaque comparaison. Dois-je ajouter une autre table, Résultats, qui a un comparison_id pour les deux enregistrements de vote ?

Here is a sample
poem_id:1 other_poem_id:2 wins:3
poem_id:2 other_poem_id:1 wins:3
so it is 50% rather than a running tally

   scope :recent, lambda {
    { :joins => "JOIN votes ON votes.poem_id = poems.id",
      :conditions => ["poems.created_at > ?", 8.days.ago],
      :order => "votes.wins DESC",
      :limit => 10 
    } 
   }

ActiveRecord::StatementInvalid : SQLite3::SQLException : ambiguë nom de colonne : created_at : SELECT
"poems".* FROM "poems" JOIN votes ON votes.poem_id = poems.id WHERE (created_at > '2010-02-12 15:12:35.764252') ORDER BY wins DESC LIMITE 10

edit : J'ai changé le schéma, voici avec quoi je travaille maintenant...
le modèle suivant permet de suivre les classements des poèmes. J'ai écrit ce premier jet hier. Il semble un peu maladroit, mais je ne sais pas encore comment l'améliorer. DailyRanking.tabulate sera appelé chaque nuit par cron. (le modèle suivant est le schéma de la comparaison).

# == Schema Information
# Schema version: 20100221120442
#
# Table name: daily_rankings
#
#  id          :integer         not null, primary key
#  poem_id     :integer
#  rank        :integer
#  percentile  :integer
#  wins        :integer
#  losses      :integer
#  draws       :integer
#  comparisons :integer
#  created_at  :datetime
#  updated_at  :datetime
#

class DailyRanking < ActiveRecord::Base
  belongs_to :poem
  class << self
    def tabulate
      # 1. get all comparisons over the past 24 hours
      comparisons = Comparison.day.all
      # 2. collect poem id for each time it wins

      # TODO make hash of "poem_id" => {:wins => a, :losses => b, :draws => c}

      a, results = 0, []

      while a < comparisons.size
        c = comparisons[a]
        if c.poem1_id == c.winner_id
          results << c.poem1_id
        elsif c.poem2_id == c.winner_id
          results << c.poem2_id
        end
        a += 1
      end
      # 3. presort by poem count

      a, unsorted_wins = 0, []

      until results.empty?
        unsorted_wins << [results.first, results.count(results.first)]
        results.delete(results.first)
      end
      # 4. sort by win count

      sorted_wins = unsorted_wins.sort { |a, b| b[1] <=> a[1] }

      # 5. repeat for losses
      a, results = 0, []

      while a < comparisons.size
        c = comparisons[a]
        if c.poem1_id == c.loser_id
          results << c.poem1_id
        elsif c.poem2_id == c.loser_id
          results << c.poem2_id
        end
        a += 1
      end

      unsorted_losses = []

      until results.empty?
        unsorted_losses << [results.first, results.count(results.first)]
        results.delete(results.first)
      end

      sorted_losses = unsorted_losses.sort { |a, b| b[1] <=> a[1] } 

      # 6. sort wins v losses

        # a. sort wins[poem] v losses[poem]
        # b. get poem and pct wins for wins[poem]
        # c. delete wins[poem] and losses[poem]
        # repeat

      unsorted_results, a = [], 0

      while a < sorted_wins.size
        poem_id = sorted_wins[a][0]
        wins = sorted_wins[a][1]
        losses = sorted_losses.select do |item| 
            item.second if item.first == poem_id
          end.compact.first.second

        unsorted_results << [ poem_id, wins / (wins + losses).to_f ]

        a += 1
      end

      # 7. sort by pct

      sorted_results = unsorted_results.sort { |a, b| b[1] <=> a[1] }

      # 8. persist rankings

      sorted_results.each_with_index do |result, index|
        ranking = find_or_create_by_rank(index + 1)
        ranking.poem_id = result.first
        ranking.save!
      end     
    end
  end
end

# == Schema Information
# Schema version: 20100221120442
#
# Table name: comparisons
#
#  id         :integer         not null, primary key
#  poem1_id   :integer
#  poem2_id   :integer
#  response   :string(4)       default("none"), not null
#  winner_id  :integer
#  loser_id   :integer
#  user_id    :integer
#  session_id :integer
#  ip         :string(15)
#  created_at :datetime
#  updated_at :datetime
#

class Comparison < ActiveRecord::Base

  scope :day,   lambda { { :conditions => ["created_at > ?", 1.day.ago] } }

end

Answer 1

Je pense qu'une requête SQL qui fonctionnerait pour vous serait quelque chose comme SELECT poems.*, percentage as ((SELECT wins FROM votes WHERE poem_id = poem.id WHERE created_at > 8.days.ago) / (SELECT wins FROM votes WHERE other_poem_id = poem.id WHERE created_at > 8.days.ago)) ORDER BY percentage DESC LIMIT 10 . Pour ce qui est de l'optimisation et de la transposition dans une portée Rails, je ne suis pas sûr.

Cependant, l'erreur que vous rencontrez est due à la manière dont le programme ["poems.created_at > ?", 8.days.ago] La condition est traduite en SQL. SQLite ne sait pas si vous cherchez poems.created_at ou votes.created_at (et d'ailleurs, d'après votre description, je pense que vous voulez votes.created_at ).