Rails 3, ActiveRecord, PostgreSQL - ".uniq" command doesn't work?

Question

I have following query:

Article.joins(:themes => [:users]).where(["articles.user_id != ?", current_user.id]).order("Random()").limit(15).uniq

and gives me the error

PG::Error: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ...s"."user_id" WHERE (articles.user_id != 1) ORDER BY Random() L...

When I update the original query to

Article.joins(:themes => [:users]).where(["articles.user_id != ?", current_user.id]).order("Random()").limit(15)#.uniq

so the error is gone... In MySQL .uniq works, in PostgreSQL not. Exist any alternative?

Answer 1

As the error states for SELECT DISTINCT, ORDER BY expressions must appear in select list. Therefore, you must explicitly select for the clause you are ordering by.

Here is an example, it is similar to your case but generalize a bit.

Article.select('articles.*, RANDOM()')
       .joins(:users)
       .where(:column => 'whatever')
       .order('Random()')
       .uniq
       .limit(15)

So, explicitly include your ORDER BY clause (in this case RANDOM()) using .select(). As shown above, in order for your query to return the Article attributes, you must explicitly select them also.

I hope this helps; good luck