sql Rand() returns same result

rubyonrails-talk
1

I have a method that calls Model.find(:first, :order => "RAND()") twice. I want it to return two different results but it always returns the same thing twice. Is there some kind of sql caching at work here? If so, can I disable for this method?

thanks!

Jason

2

First of all, I'm assuming you are calling rand() (lowercase). rand() returns a value less than between 0 and 1. Using this as the call to :order tells the database to use the column reference (rather than a name). In MySQL, if you pass a non-integer column value, it gets ignored. (Not sure how other databases handle it).

I don't really understand what you are trying to randomize in your order parameter. You should probably get the count of the records in your table and generate a random number between 1 and that count. Then use offset to select that record.

3

Speaking strictly from a MySQL perspective --

First of all, I'm assuming you are calling rand() (lowercase).

Case isn't significant.

I don't really understand what you are trying to randomize in your order parameter. You should probably get the count of the records in your table and generate a random number between 1 and that count. Then use offset to

? The syntax below works just fine to return a random instance of Model.

I have a method that calls Model.find(:first, :order => "RAND()") twice. I want it to return two different results but it always returns the same thing twice.

What it *doesn't* do is guarantee that any two randomly selected instances won't be the same :slight_smile:

Quick example:

(1..20).each do

?> @up = UserProfile.find(:first, :order => "rand()")

puts @up.id end

8 8 11 1 5 1 13 13 2 6 17 5 4 9 6 17 8 10 7 5 => 1..20

If the two really need to be different, you'll have to address that with application logic.

FWIW,

4

You may be getting stuck in the query cache. Try this:

Model.uncached { Model.find(:first, :order => "RAND()") }

Which will shut off query caching for that call.

--Matt Jones

5

My bad - I didn't notice the quotes around "rand()" in the first post.

I thought a random number was being passed to the database rather than a function.