RAND() for MySQL, RANDOM() for SQLite

Rails
1

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?

2

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

3

Thanks! That did the trick. :slight_smile: But I’m still curious as to how you
would generally abstract away something you want to do independent of
the database.

4

Philip H. wrote:

Thanks! That did the trick. :slight_smile: 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 RANDOM is you can seed it with today’s
date, to
rotate your content daily…

5

Thanks! That did the trick. :slight_smile: 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.