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 Kopanas john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org http://www.kopanas.com http://www.cusec.net http://www.soen.info --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
On Oct 15, 2006, at 10:48 PM, John Kopanas 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 :-).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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
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 Kopanas 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 :-).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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
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? :-) On 10/15/06, Xavier Noria <fxn-xlncskNFVEJBDgjK7y7TUQ@public.gmane.org> wrote:> > 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 Kopanas 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 :-). > > 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 > > > > >-- John Kopanas john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org http://www.kopanas.com http://www.cusec.net http://www.soen.info --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
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 Kopanas 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 Kopanas > john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org > > http://www.kopanas.com > http://www.cusec.net > http://www.soen.info-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Then why did you ask for three users who has_many pictures? :-)
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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---
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... --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
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 <fxnoria-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > 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 > > > > >-- John Kopanas john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org http://www.kopanas.com http://www.cusec.net http://www.soen.info --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---