I am trying to pull out rows from a postgres database, I can pull them out desc but when I try random I get a Syntax error near random.
Error
PG::Error: ERROR: syntax error at or near "rand"
LINE 1: ... "hashtags".* FROM "hashtags" ORDER BY tweet_id rand LIMIT...
^
: SELECT "hashtags".* FROM "hashtags" ORDER BY tweet_id rand LIMIT 4
Code to pull it out
<div id="hashtags">
<% Hashtag.order("tweet_id desc").limit(4).each do |hashtag| %>
<blockquote><%= hashtag.content %></blockquote>
<div class="from">— @<%= hashtag.screen_name %></div>
<% end %>
</div>
To fetch random entries from your database you have a few options. Here's a couple
1st approach
This will take 4 random entries out of your DB using SQL.
Hashtag.order("RANDOM()").limit(4)
2nd approach:
You can also use ActiveRecord sample() method to retrieve 4 random rows.
Hashtag.all.sample(4)
As of speed and efficiency; I made a mini-benchmark and tested two commands on my own db (contains 500 records). The first approach (as expected) was more than twice faster than the second approach.
SQL: 1.8ms
Sample Method: 4.2ms
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With