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 1 down vote favorite

Im trying to query my db for records that are similar to the currently viewed record (based on taggings), which I have working but I would like to randomize the order.

my development environment is mysql so I would do something like:

@tattoos = Tattoo.tagged_with(tags, :any => true).order("RAND()").limit(6)

which works, but my production environment is heroku which is using postgresql so I tried using this:

@tattoos = Tattoo.tagged_with(tags, :any => true).order("RANDOM()").limit(6)

but I get the following error:

ActionView::Template::Error (PGError: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

SELECT  DISTINCT tattoos.* FROM "tattoos" JOIN taggings 
tattoos_taggings_color_fantasy_newschool_nerdy_tv_477  ON 
tattoos_taggings_color_fantasy_newschool_nerdy_tv_477.taggable_id = tattoos.id AND 
tattoos_taggings_color_fantasy_newschool_nerdy_tv_477.taggable_type = 'Tattoo' WHERE 
(tattoos_taggings_color_fantasy_newschool_nerdy_tv_477.tag_id = 3 OR 
tattoos_taggings_color_fantasy_newschool_nerdy_tv_477.tag_id = 4 OR 
tattoos_taggings_color_fantasy_newschool_nerdy_tv_477.tag_id = 5 OR 
tattoos_taggings_color_fantasy_newschool_nerdy_tv_477.tag_id = 24 OR 
tattoos_taggings_color_fantasy_newschool_nerdy_tv_477.tag_id = 205) ORDER BY RANDOM() LIMIT 6):
share|improve this question
Just a quick comment that for data sets over a few thousand rows, order by random() limit x is VERY inefficient. To the point that once you've got 10k to 1M rows it'll get too slow to be acceptable. – Scott Marlowe May 29 '12 at 4:42

2 Answers

up vote 2 down vote accepted

After analyzing the query more closely, I have to correct my first draft. The query would require a DISTINCT or GROUP BY the way it is.

The (possibly) duplicate tattoos.* come from first joining to (possibly) multiple rows in the table taggings. Your query engine then tries to get rid of such duplicates again by using DISTINCT - in a syntactically illegal way.

DISTINCT basically sorts the resulting rows by the resulting columns from left to right and picks the first for each set of duplicates. That's why the leftmost ORDER BY column have to match the SELECT list.

MySQL is more permissive and allows the non-standard use of DISTINCT, but PostgreSQL throws an error.

ORMs often produce ineffective SQL statements (they are just crutches after all). However, if you use appropriate PostgreSQL libraries, such an illegal statement shouldn't be produced to begin with. I am no Ruby expert, but something's fishy here.

The query is also very ugly and inefficient.

There are several ways to fix it. For instance:

SELECT * 
FROM  (<query without ORDER BY and LIMIT>) x
ORDER  BY RANDOM()
LIMIT  6

Or, better yet, rewrite the query with this faster, cleaner alternative doing the same:

SELECT ta.*
FROM   tattoos ta
WHERE  EXISTS (
    SELECT 1
    FROM   taggings t
    WHERE  t.taggable_id = ta .id
    AND    t.taggable_type = 'Tattoo'
    AND    t.tag_id IN (3, 4, 5, 24, 205)
    )
ORDER  BY RANDOM()
LIMIT  6;

You'll have to implement it in Ruby yourself.

share|improve this answer
How do I drop DISTINCTfrom that query? It seems that postgresql adds it automatically. – rugbert May 27 '12 at 17:22
@rugbert: I am no expert with Ruby, but I provided an improved answer with explanation and query alternatives. – Erwin Brandstetter May 27 '12 at 18:11
Cool thanks, I can work with that. Question tho, how are you pulling data from the tags table using "ta" and not tags? – rugbert May 27 '12 at 19:45
@rugbert: Cool. :) By assigning a table alias here: FROM tattoos ta - which is short for FROM tattoos AS ta. More details in the excellent manual here. – Erwin Brandstetter May 27 '12 at 19:48
up vote 0 down vote

not sure about the random, as it should work. But take a note of http://railsforum.com/viewtopic.php?id=36581

which has code that might suit you

/lib/agnostic_random.rb
module AgnosticRandom
  def random
    case DB_ADAPTER
      when "mysql" then "RAND()"
      when "postgresql" then "RANDOM()"
    end
  end
end

/initializers/extend_ar.rb (name doesn't matter)
ActiveRecord::Base.extend AgnosticRandom
share|improve this answer
Yeah I saw that post, but I'll still get an error with postgresql. also, I have conflict with that module and my aws gem – rugbert May 27 '12 at 17:17

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.