Select a Radom list of Max 3 Users from the DB

Here are my models:

class User < ActiveRecord::Base
  has_many :pictures
end

class Picture < ActiveRecord::Base
  belongs_to :user
end

How do I get a random list of 3 users who have at least one picture
associated with them using ActiveRecord? Sounds fun... I know :-).

A possible (untested) approach that does not rely on the usual SQL idiom for random rows that orders the table. Assumes the set of user IDs is not sparse:

   require 'enumerator'

   ntrials = 0
   max_id = User.maximum("id")

   loop do
     ntrials += 1
     random_users = []

     [1, rand(max_id), rand(max_id)].sort.each_cons(2) do |a, b|
       user = User.find_by_sql(<<-SQL
         SELECT u.*
         FROM users u, pictures p
         WHERE u.id BETWEEN a AND b
           AND u.id = p.user_id
         LIMIT 1
       SQL
       )
       break if user.nil?
       # the unless condition takes care of corner cases when
       # random limits coincide
       random_users << user unless random_users.include?(user)
     end
     redo if random_users.size < 3 && ntrials < MAX_TRIALS
   end

That needs extra code for edge cases, but you see the idea.

-- fxn

Looks like my emails are not getting to the list today. I forward this answer to your inbox directly.

-- fxn

Here are my models:

class User < ActiveRecord::Base
  has_many :pictures
end

class Picture < ActiveRecord::Base
  belongs_to :user
end

How do I get a random list of 3 users who have at least one picture
associated with them using ActiveRecord? Sounds fun... I know :-).

A possible (untested) approach that does not rely on the usual SQL idiom for random rows that orders the table. Assumes the set of user IDs is not sparse:

   require 'enumerator'

   ntrials = 0
   max_id = User.maximum("id")

   loop do
     ntrials += 1
     random_users = []

     [1, rand(max_id), rand(max_id)].sort.each_cons(2) do |a, b|
       user = User.find_by_sql(<<-SQL
         SELECT u.*
         FROM users u, pictures p
         WHERE u.id BETWEEN a AND b
           AND u.id = p.user_id
         LIMIT 1
       SQL
       )
       break if user.nil?
       # the unless condition takes care of corner cases when
       # random limits coincide
       random_users << user unless random_users.include?(user)
     end
     redo if random_users.size < 3 && ntrials < MAX_TRIALS
   end

That needs extra code for edge cases, but you see the idea.

-- fxn

I am sure there is a much easier way of doing it. I just really need
to get a random list of three pictures from the Picture model. (note:
right now each user only has one picture associated with him/her).

Any other suggestions? :slight_smile:

Then why did you ask for three users who has_many pictures? :slight_smile:

In that case the same approach needs simpler code:

  require 'enumerator'

  # assumes there are at least three pictures in the database
  max_id = Picture.maximum("id")
  loop do
    pictures = []

    [1, 2+rand(max_id-2), max_id].sort.each_cons(2) do |a, b|
      picture = Picture.find(
        :first,
        :conditions => "id BETWEEN #{a} AND #{b}",
        :limit => 1
      )
      break if picture.nil?
      pictures << picture
    end
    pictures.size < 3 ? redo : break
  end

If you don't like this approach Google for "random row SQL".

-- fxn

Untested...

Replace the search code with:

picture = Picture.find(:first, :order => "RAND()")

If you're using MySQL. (RANDOM() for Postgre and NEWID() for
SQLServer.)

You'll still need to check for multiples. If you're scanning by
picture, you'll have to check the related user as well. Otherwise,
check for 'has_picture' or 'picture is not null' in the find statement
:conditions, depending on how you handle nulls in your database.

I'm guessing you don't care too much for portability...

fxn wrote:

    [1, 2+rand(max_id-2), max_id].sort.each_cons(2) do |a, b|

The call to sort is redundant now.

-- fxn

Heh, that code is wrong.

I was trying to write inline a rough idea I had in mind which was to
use rand in the Ruby side to avoid ordering the table, but messed it
up. Sorry!

What I was trying to do, really, was to pick three random ids in some
suitable range. If the table is not sparse we will fetch 3 random
records in one shot most of the time, so we'd be done with a single
find(:all) call with the random ids ORed. You still need to deal with
duplicates as in the RAND() SQL idiom, and need a lttle extra code to
loop in case some random number is not an id in the table (unlikely if
pictures are rarely deleted).

-- fxn

Thanks guys! I find the:

Picture.find(:all, :order => 'RAND()', :limit => 3)

Solution is very sleak and the best solution for what I need right
now. Thanks everyone :-).

If you want to relate it to you user you could also write something
like this

def image
@user=user.find(:all)
@image=user.find(:all, :limit=>3, :order=>"RAND()",
:conditions=>["user_id=?", @user.id])
end

Then you could list your user and images... Might need some tweaking,
but hopefully you get the idea.