Databases
StackOverflow2013
Postsdbo
POImplementing a Leaderboard
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POImplementing a Leaderboard
    text
    copied!<p>Users <a href="http://rapexegesis.com" rel="nofollow noreferrer">on my site</a> create annotations for rap lyrics (<a href="http://RapExegesis.com/2027" rel="nofollow noreferrer">example</a>). I want to create a leaderboard to reward the people who create the most annotations.</p> <p>The leaderboard should track how many annotations each user has created overall, as well as how many he has created in the past week, day, etc.</p> <p>I have no problem implementing the overall leaderboard:</p> <pre><code>@users = User.all &lt;table&gt; &lt;tr&gt; &lt;th&gt;Contributor&lt;/th&gt; &lt;th&gt;Annotations&lt;/th&gt; &lt;/tr&gt; &lt;% @users.sort_by{|u| u.annotations.size }.reverse.each do |u| %&gt; &lt;tr&gt; &lt;td&gt;&lt;%= u %&gt;&lt;/td&gt; &lt;td&gt;&lt;%= u.annotations.size %&gt;&lt;/td&gt; &lt;/tr&gt; &lt;% end %&gt; &lt;/table&gt; </code></pre> <p>But when I try to implement (say) the daily scoreboard, I'm repeating code and the operation is painfully slow (because it has to iterate through every annotation in memory rather than relying on database sorting / counting):</p> <pre><code>&lt;table&gt; &lt;tr&gt; &lt;th&gt;Contributor&lt;/th&gt; &lt;th&gt;Annotations&lt;/th&gt; &lt;/tr&gt; &lt;% @users.sort_by{|u| u.annotations.select{|a| a.created_at &gt; 1.day.ago }.size }.reverse.each do |u| %&gt; &lt;tr&gt; &lt;td&gt;&lt;%= u %&gt;&lt;/td&gt; &lt;td&gt;&lt;%= u.annotations.select{|a| a.created_at &gt; 1.day.ago }.size %&gt;&lt;/td&gt; &lt;/tr&gt; &lt;% end %&gt; &lt;/table&gt; </code></pre> <p>What's the best way to implement a daily / weekly scoreboard?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload