I''m trying to pick some random rows from my database; for development I use SQLite and for production I use MySQL. However, as the title suggests, they both have different ways to get random rows. How can I abstract this away? --~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
> I''m trying to pick some random rows from my database; for development > I use SQLite and for production I use MySQL. However, as the title > suggests, they both have different ways to get random rows. How can I > abstract this away?Psuedo-ally: - get the total number of rows via Foo.count. - Use ruby to get a random number b/n 0 and the total. - Construct a query using :limit => 1 and :offset => random_number. There''s probably an edge case where you might fall off the end in there, but I''ll let you confirm that. You don''t want to use RAND()/RANDOM() if you can avoid it as mysql will fetch *every* row, assign each a random number sort it, and return the first. Not efficient with many rows. -philip --~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
Thanks! That did the trick. :) But I''m still curious as to how you would generally abstract away something you want to do independent of the database. On Mar 16, 1:34 pm, Philip Hallstrom <phi...-LSG90OXdqQE@public.gmane.org> wrote:> > I''m trying to pick some random rows from my database; for development > > I use SQLite and for production I use MySQL. However, as the title > > suggests, they both have different ways to get random rows. How can I > > abstract this away? > > Psuedo-ally: > > - get the total number of rows via Foo.count. > - Use ruby to get a random number b/n 0 and the total. > - Construct a query using :limit => 1 and :offset => random_number. > > There''s probably an edge case where you might fall off the end in > there, but I''ll let you confirm that. > > You don''t want to use RAND()/RANDOM() if you can avoid it as mysql > will fetch *every* row, assign each a random number sort it, and > return the first. Not efficient with many rows. > > -philip--~--~---------~--~----~------------~-------~--~----~ 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> Thanks! That did the trick. :) But I''m still curious as to how you > would generally abstract away something you want to do independent of > the database.By doing exactly what you just did. Or sticking to pure SQL (can''t think of the standard, but there is one) and using nothing else.> On Mar 16, 1:34 pm, Philip Hallstrom <phi...-LSG90OXdqQE@public.gmane.org> wrote: >>> I''m trying to pick some random rows from my database; for >>> development >>> I use SQLite and for production I use MySQL. However, as the title >>> suggests, they both have different ways to get random rows. How >>> can I >>> abstract this away? >> >> Psuedo-ally: >> >> - get the total number of rows via Foo.count. >> - Use ruby to get a random number b/n 0 and the total. >> - Construct a query using :limit => 1 and :offset => random_number. >> >> There''s probably an edge case where you might fall off the end in >> there, but I''ll let you confirm that. >> >> You don''t want to use RAND()/RANDOM() if you can avoid it as mysql >> will fetch *every* row, assign each a random number sort it, and >> return the first. Not efficient with many rows. >> >> -philip > >--~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
Philip Hallstrom wrote:>> Thanks! That did the trick. :) But I''m still curious as to how you >> would generally abstract away something you want to do independent of >> the database. > > By doing exactly what you just did. Or sticking to pure SQL (can''t > think of the standard, but there is one) and using nothing else.Briefly, database independence is AR''s job, not yours. Coding for multiple databases is a form of "premature complexity". Wait until your boss is actually paying you to cover a new database, before wasting time on it. Your predictions would only cause trouble anyway. And the great thing about RAND[OM]() is you can seed it with today''s date, to rotate your content daily... --~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---