title: Cassandra Real Time Content Ranking Solution
body: <p>I would like to implement a real time content ranking system on cassandra 1.2  CQL 3.0</p>
  
  <p>Example : Providing the top 10 comments on a news based on user vote</p>
  
  <ul>
  <li>user can comment a news</li>
  <li>user can vote +1 or -1 on each comment</li>
  </ul>
  
  <p>Maybe 100, 1000, 10k, 100k comments by content</p>
  
  <p>So what i need is to get real time (or 5min delay) the top 10 comments based on vote score</p>
  
  <p>[real example youtube comment]</p>
  
  <p>My solution is :</p>
  
  <ul>
  <li>storing vote counter on counter table (content_counter)</li>
  <li>create a rank table</li>
  </ul>
  
  <p>create table content_comment_rank (
  content_id timeuuid,
  score bigint,
  com_id timeuuid,
  PRIMARY KEY (content_id, score, com_id)
  ) WITH CLUSTERING ORDER BY (score DESC)</p>
  
  <ul>
  <li>all data of a content_id is stored on 1 ROW ordered by score desc</li>
  <li>I only have i this table comment with at least 1 vote</li>
  </ul>
  
  <p>So what happen when a user vote</p>
  
  <ul>
  <li>SELECT score FROM content_counter where content_id='x' (R: 123)</li>
  <li>UPDATE content_counter SET score=score+1 (124) (or -1)</li>
  </ul>
  
  <p>IN BATCH : </p>
  
  <ul>
  <li>DELETE content_comment_rank WHERE content_id='x' AND score=123 AND com_id='y'</li>
  <li>INSERT content_comment_rank (content_id,score,com_id) VALUES ('x',124,'y')</li>
  </ul>
  
  <p>So having top 10 comments of a content is </p>
  
  <p>SELECT * FROM content_comment_rank WHERE content_id='x' LIMIT 10</p>
  
  <p>It's pretty easy BUT i'm not sure i'm doing well because :</p>
  
  <ul>
  <li><p>How to deal with replication and Concurrent vote</p>
  
  <p>Maybe by updating 1 time in batch each day (It's like that vote on video youtube is working)</p></li>
  <li><p>Maybe there is a better way....</p></li>
  </ul>
  
  <p>We need "cluster ordering on counter table"..... (all done in 1 atom query) but doesn't existe (or not...)</p>
  
  <p>What's your advice on my solution ?</p>
  
  <p>Thank you,</p>