Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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">&mdash; @<%= hashtag.screen_name %></div>
 <% end %>
</div>
share|improve this question
1  
Er, your code example isn't using random(); it's using desc. Is that the right code? Also, as an aside, your query should go in your controller, not your view. Finally, I suggest testing stuff like this in the Rails console. – Isaac Cambron Nov 30 '12 at 7:59
How would I add it to the controller then call it? – thebusiness11 Nov 30 '12 at 8:03
just put in @random_hashtags = Hashtag.order(...).limit(4), and then just refer to it in your view like @random_hashtags.each do.... – Isaac Cambron Nov 30 '12 at 8:26

1 Answer

up vote 1 down vote accepted

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
share|improve this answer
This was what I was looking for. Thanks! – thebusiness11 Nov 30 '12 at 8:02
Is this an advantage of choosing one over the other? – thebusiness11 Nov 30 '12 at 8:09
Actually I know why that doesn't work and why I was trying to do <% Hashtag.order("tweet_id rand").limit(4).each do |hashtag| %> Was so it would only pull out random rows from tweet_id matched a certain value – thebusiness11 Nov 30 '12 at 8:14
The second approach is slower because it's the Ruby code that picks the random and not SQL. It gets every row in hashtags table and randomly select 4, never use it. – Jirico Mar 5 at 20:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.