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?
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
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.
Thanks! That did the trick.
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.
Philip Hallstrom wrote:
Thanks! That did the trick.
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...