Forum: Ruby on Rails RAND() for MySQL, RANDOM() for SQLite

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Af25c598c54850cd55e07b1d060df9e1?d=identicon&s=25 Mike Chai (mlchai)
on 2009-03-16 21:27
(Received via mailing list)
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?
2505b282d57c29be797dc35b245adb4c?d=identicon&s=25 Philip Hallstrom (Guest)
on 2009-03-16 21:35
(Received via mailing list)
> 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
Af25c598c54850cd55e07b1d060df9e1?d=identicon&s=25 Mike Chai (mlchai)
on 2009-03-16 21:45
(Received via mailing list)
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.
2505b282d57c29be797dc35b245adb4c?d=identicon&s=25 Philip Hallstrom (Guest)
on 2009-03-16 21:54
(Received via mailing list)
> 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.
Aafa8848c4b764f080b1b31a51eab73d?d=identicon&s=25 Phlip (Guest)
on 2009-03-17 05:33
(Received via mailing list)
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...
This topic is locked and can not be replied to.