I''ve got a bunch of records, and I''d like to display 5 random ones. If I just find_all and limit it to 5, all I get are the first 5 records in the db. How can I get random ones instead?
Not the slickest method but you could do a find_all get the record count create 5 random numbers in the correct range grab those records out of the array. John W Higgins develop-U23jnKMpDSxBDgjK7y7TUQ@public.gmane.org On Tue, 2005-04-19 at 21:04 -0600, Pat Maddox wrote:> I''ve got a bunch of records, and I''d like to display 5 random ones. > If I just find_all and limit it to 5, all I get are the first 5 > records in the db. How can I get random ones instead? > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails
On 4/20/05, John W Higgins <develop-U23jnKMpDSxBDgjK7y7TUQ@public.gmane.org> wrote:> Not the slickest method but you could do a > > find_all > get the record count > create 5 random numbers in the correct range > grab those records out of the array. > > John W Higgins > develop-U23jnKMpDSxBDgjK7y7TUQ@public.gmane.org > > On Tue, 2005-04-19 at 21:04 -0600, Pat Maddox wrote: > > I''ve got a bunch of records, and I''d like to display 5 random ones. > > If I just find_all and limit it to 5, all I get are the first 5 > > records in the db. How can I get random ones instead?If you''re using mysql you can use Blah.find(:all, :order=>"RAND() ASC", :limit=>5)> > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Cheers Koz
> I''ve got a bunch of records, and I''d like to display 5 random ones. > If I just find_all and limit it to 5, all I get are the first 5 > records in the db. How can I get random ones instead?I think you need to do that with SQL, through that means it''s going to be database-dependent: MyClass.find_by_sql("SELECT * FROM mytable ORDER BY random() LIMIT 5") did it for me (can anyone come up with portable SQL?). Or, this may be good enough: wanted = 5 offset = rand(MyClass.count - wanted) MyClass.find_all(nil, nil, [wanted, offset]) With this, you''ll get random set of records, but content of each set is sequential. Best Regards, -- Taisuke Yamada
On 20/04/2005, at 1:04 PM, Pat Maddox wrote:> I''ve got a bunch of records, and I''d like to display 5 random ones. > If I just find_all and limit it to 5, all I get are the first 5 > records in the db. How can I get random ones instead?http://wiki.rubyonrails.com/rails/show/HowtoSelectRandomRecords
On 20.4.2005, at 07:33, Taisuke Yamada wrote:>> I''ve got a bunch of records, and I''d like to display 5 random ones. >> If I just find_all and limit it to 5, all I get are the first 5 >> records in the db. How can I get random ones instead? > > I think you need to do that with SQL, through that means it''s > going to be database-dependent: > > MyClass.find_by_sql("SELECT * FROM mytable ORDER BY random() LIMIT > 5")Again the usual warning: sorting by random() will force a sequential scan on the records. It might slow the system down pretty fast when the amount of records grows larger. The method suggested in Tim''s link to wiki doesn''t have that problem. //jarkko -- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Hmm, what sample on wiki page does is: 1. Fetch all primary keys from the table 2. Select N keys randomly 3. Fetch data, using above selected keys So doesn''t step #1 also require sequential scan of all records as well? It also requires full transfer over the network. Yes, wiki example do have WHERE clause, but same thing can be done more efficiently by "WHERE expr ORDER BY random()". I think which is better depends, as you never want to fetch and transfer all primary keys if you have millions of records. I believe there should be better SQL-based solution though...>> MyClass.find_by_sql("SELECT * FROM mytable ORDER BY random() LIMIT 5") > > Again the usual warning: sorting by random() will force a sequential > scan on the records. It might slow the system down pretty fast when the > amount of records grows larger. The method suggested in Tim''s link to > wiki doesn''t have that problem.-- Taisuke Yamada
On 20.4.2005, at 13:23, Taisuke Yamada wrote:> Hmm, what sample on wiki page does is: > > 1. Fetch all primary keys from the table > 2. Select N keys randomly > 3. Fetch data, using above selected keys > > So doesn''t step #1 also require sequential scan of all records > as well?No, it does no sorting at all. The order in which the records are fetched is not specified so it uses the fastest algorithm available to get the records. And even if there were some sorting going on, we''re talking about primary key here so there''s always an index for that field. So instead of sequential scan we now talk about index scan, which is of course faster by orders of magnitude. Oh, and you can''t obviously create an index for random() :-) Just to make it sure, sorting a large set of records by a non-index field is one of the slowest operations an rdbms can do.> It also requires full transfer over the network. Yes, > wiki example do have WHERE clause, but same thing can be done > more efficiently by "WHERE expr ORDER BY random()".Right. I don''t know about the current situation but many rdbms''s have been reported having problems to optimize that kind of queries.> I think which is better depends, as you never want to fetch and > transfer all primary keys if you have millions of records. I believe > there should be better SQL-based solution though...You''re right, it depends. But what I''ve found is that you reach the limits of the random() method a lot faster than the other method, already when talking about thousands of records. But anyway, both of them work and if you''re concerned about the performance, fetch a bunch of random records every now and then, cache the records and then use ruby to display one of them. Oh yes, and SQL should definitely reflect the *real* needs of a random method. The current solution is definitely quite not there. //jarkko -- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails