I was trying to make a Quote model that could feed my webapp with
random quotes. I posted about this at Random in Ruby on Rails | Nuno Job
After creating the model i entered ./script/console and tried
Quote.find(:random)
This didn't work so I looked for another solution. Some of those
where:
Quote.find(:all)[rand(Quote.count)] # Works but fetches all quotes and does 2 sql statetments
Quote.find_by_id rand(Quote.count) + 1 # Doesn't work if somebody deletes a quote and uses 2 sql statements.
Quote.find(:first, :order => "RAND()") # RAND is for mysql only. There no way to pass the correct sql to everyadapter
After this my friend António Medeiros made a way to do this, for my
model:
def self.find(*args)
if args.first.to_s == "random"
super(:first, :order => self.random_command)
else
super
end
end
def self.random_command
case self.connection.class
when ActiveRecord::ConnectionAdapters::MysqlAdapter
'RAND()'
else
# Other ways to do RAND for diferent adapters.
'RANDOM()'
end
end
end
puts Quote.find(:random).quote
The next step seemed to be suggesting a patch to rails core. However
I'm still a rails rookie and don't have that necessary knowledge to
understand all of that code. Can you tell me if this is already on
rails core? Can someone add it please. It seems rather easy.
The next step seemed to be suggesting a patch to rails core. However
I'm still a rails rookie and don't have that necessary knowledge to
understand all of that code. Can you tell me if this is already on
rails core? Can someone add it please. It seems rather easy.
The problem with this is that order by RAND is extremely inefficient
as it has to evaluate RAND() for every row in the table. I'm hesitant
to stick something this ... dangerous into the core framework itself.
I guess that there are other tweaks for other database systems (like
db2, oracle, postgre, sqlite). So in the core we could have the
tweaked fast versions of random for each of the dbms. Then the user
would only need to
I read Jan's entry as well a while ago, and it's very interesting. However, it's also pretty laborious and not trivial to implement across different databases. So I feel this is a good candidate for a plugin but certainly not for core, at least at this point.
Nuno, I support this in a form of a plugin. It is definitely useful. But, because of various approaches needed because of different databases (and a lot of people disagreeing which selects are optimized), I don’t think it will ever be fit for core (as Koz suggests).
For tables that are have less rows than thousands, I think that selecting (and caching) the list of IDs always performs well. Then, use rand from Ruby to pick a random record. I don’t think you have a thousand quotes
When you release a plugin, there will be people who will contribute more optimized solutions based on their needs.
