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.
Mike C. (Guest)
on 2009-03-16 22: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?
Philip H. (Guest)
on 2009-03-16 22: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
Mike C. (Guest)
on 2009-03-16 22: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.
Philip H. (Guest)
on 2009-03-16 22: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.
Phlip (Guest)
on 2009-03-17 06:33
(Received via mailing list)
Philip H. 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.