Select a Radom list of Max 3 Users from the DB

Rails
1

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 :-).

–
John K.
[email protected]

http://www.kopanas.com

http://www.soen.info

2

On Oct 15, 2006, at 10:48 PM, John K. wrote:

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

3

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:

On 10/15/06, Xavier N. [email protected] wrote:

 ntrials += 1
   )

– fxn

–
John K.
[email protected]

http://www.kopanas.com

http://www.soen.info

4

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

– fxn

On Oct 15, 2006, at 10:48 PM, John K. wrote:

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

5

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

6

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…

7

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

8

If you are using MySQL, then this will get you three random users.

User.find(:all, :order => ‘RAND()’, :limit => 3)

three random pictures:

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

Sorry it’s MySQL specific - if you’re using another db, I’m sure there’s
some similar functionality for grabbing random rows, but I’m not as
familiar with the other db’s.

c.

John K. wrote:

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 :-).

–
John K.
[email protected]

http://www.kopanas.com
http://www.cusec.net
http://www.soen.info

9

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

10

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.

11

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 :-).

On 10/15/06, fxn [email protected] wrote:

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

–
John K.
[email protected]

http://www.kopanas.com

http://www.soen.info