Sql Rand() returns same result


#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

On Sat, May 23, 2009 at 10:06 PM, E. Litwin removed_email_address@domain.invalid
wrote:

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,

Hassan S. ------------------------ removed_email_address@domain.invalid


#4

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.

On May 24, 8:10 am, Hassan S. removed_email_address@domain.invalid


#5

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 J.