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

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?

Answers


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


Just to enrich the thread with more examples, in case you have nested relations in the query, you can try with the following statement.

Person.find(params[:id]).cars.select('cars.*, lower(cars.name)').order("lower(cars.name) ASC")

In the given example, you're asking all the cars for a given person, ordered by model name (Audi, Ferrari, Porsche)

I don't think this is a better way, but may help to address this kind of situation thinking in objects and collections, instead of a relational (Database) way.

Thanks!


I assume that the .uniq method is translated to a DISTINCT clause on the SQL. PostgreSQL is picky (pickier than MySQL) -- all fields in the select list when using DISTINCT must be present in the ORDER_BY (and GROUP_BY) clauses.

It's a little unclear what you are attempting to do (a random ordering?). In addition to posting the full SQL sent, if you could explain your objective, that might be helpful in finding an alternative.


I just upgraded my 100% working and tested application from 3.1.1 to 3.2.7 and now have this same PG::Error.

I am using Cancan...

@users = User.accessible_by(current_ability).order('lname asc').uniq

Removing the .uniq solves the problem and it was not necessary anyway for this simple query.

Still looking through the change notes between 3.1.1 and 3.2.7 to see what caused this to break.


Need Your Help

Interpret enter as tab WPF

wpf keyboard-input

I want to interpret Enter key as Tab key in whole my WPF application, that is, everywhere in my application when user press Enter I want to focus the next focusable control,except when button is fo...

Can anyone provide a good "idiot's guide" to creating an installer in VS2008 (C#) Pro?

c# wpf visual-studio-2008 installer console

I have Visual C# 2008 Professional and have developed the first half of a C# application (console mode) with the second half in progress now (GUI).