CODEWITHSUNDEEP
databaseruby-on-rails-3postgresqlactiverecorduniq
user984621
user984621

Reputation: 48453

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?

Upvotes: 15

Views: 8570

Answers (4)

vanboom
vanboom

Reputation: 1332

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.

Upvotes: 0

DavidSilveira
DavidSilveira

Reputation: 171

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!

Upvotes: 2

user568278
user568278

Reputation:

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

Upvotes: 31

Tom Harrison
Tom Harrison

Reputation: 14018

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.

Upvotes: 1

Related Questions