The Better Way To Do Random using Scope

post.rb

# before
# from http://jan.kneschke.de/projects/mysql/order-by-rand

def self.pick
  return Post.find_by_sql("SELECT * FROM posts as r1 JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM posts)) AS gid) AS r2 WHERE r1.id >= r2.gid and r1.status = ‘published’ ORDER BY r1.id ASC LIMIT 1;").first;
end

# after
# ex. Post.rand.published.limit(5)

# 
  scope :rand, lambda { |code| {
    :joins => "join (SELECT CEIL(RAND() * (SELECT MAX(id) FROM posts)) AS gid) AS r2",
    :conditions => "posts.id > r2.gid",
    # :order => "id ASC"  # do not order here, will cause scope chain very slow, especially with published scope.
    }
  }

# about 0.6ms
#  Post Load (0.6ms)  SELECT SQL_NO_CACHE `posts`.* FROM `posts` join (SELECT CEIL(RAND() * (SELECT MAX(id) FROM posts)) AS gid) AS r2 WHERE `posts`.`aasm_state` = 'published' AND (posts.id > r2.gid) AND (published_at_unix_time <= 1309789491) ORDER BY published_at_unix_time DESC LIMIT 5

# ====


# Many to many, ex. game has many posts, through game_posts

# pure game.posts.rand.limit(5) is fast, about 1 ms
# Post Load (1.0ms)  SELECT SQL_NO_CACHE `posts`.* FROM `posts` INNER JOIN `game_posts` ON `posts`.id = `game_posts`.post_id join (SELECT CEIL(RAND() * (SELECT MAX(id) FROM posts)) AS gid) AS r2 WHERE ((`game_posts`.game_id = 3)) AND (posts.id > r2.gid) LIMIT 5

# but game.posts.published.limit(5) is very slow , about 148ms

# Post Load (148.5ms)  SELECT SQL_NO_CACHE `posts`.* FROM `posts` INNER JOIN `game_posts` ON `posts`.id = `game_posts`.post_id join (SELECT CEIL(RAND() * (SELECT MAX(id) FROM posts)) AS gid) AS r2 WHERE `posts`.`aasm_state` = 'published' AND ((`game_posts`.game_id = 3)) AND (posts.id > r2.gid) AND (published_at_unix_time <= 1309790192) ORDER BY published_at_unix_time DESC LIMIT 5

# ====

# you should use Post.rand_by_game(3).published.limit(5)


  scope :rand_by_game, lambda { |game_id| {
    :joins => "join (SELECT CEIL(RAND() * (SELECT MAX(id) FROM posts)) AS gid) AS r2",
    :conditions => "posts.id > r2.gid and posts.id in (select post_id from game_posts where game_id = #{game_id})",
    }
  }

# about 0.8ms
#  Post Load (0.8ms)  SELECT SQL_NO_CACHE `posts`.* FROM `posts` join (SELECT CEIL(RAND() * (SELECT MAX(id) FROM posts)) AS gid) AS r2 WHERE `posts`.`aasm_state` = 'published' AND (posts.id > r2.gid and posts.id in (select post_id from game_posts where game_id = 3)) AND (published_at_unix_time <= 1309790467) ORDER BY published_at_unix_time DESC LIMIT 5

# =====

      def published
        where(:aasm_state => "published").where(["published_at_unix_time <= ?", Time.zone.now.to_i]).order("published_at_unix_time DESC")
      end
    
Post.count # => 9413