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?

Fjord

Are you sure query with uniq returns different result? Please show actual SQL queries for each variant (use sql method). – Kafir

I am sure. When I used this query with MySQL database, so without .uniq it returned me also the same row, but with .uniq always unique row. In PostgreSQL if I use .uniq, I get the error specified above, if I don't use .uniq, so the error is gone, but I am getting also the same rows from DB. – Fjord

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

Felicidad

Why must you even add what you are ordering by to the select clause in the first place in postgresql. If you are using a join, then the sql should be smart enough to know what to order by without explicitly declaring it in the select clause (especially since the select clause is intended to limit columns returned). – Joint

Doesn't the inclusion of RANDOM() completely negate the DISTINCT because it's taking a set of completely unique values into account? And as such you end up with duplicates records just as if you hadn't used the DISTINCT in the first place? – Frilling

Answer 2

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!

Taber

Answer 3

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.

Lorie

Probably you're right, but exist any elegant way to integrate in the query above DISTINCT clausule? My current query is "pretty short", I can use find_by_sql and there straight forward to set DISTINCT clausule, but in this case would be the query much bigger. – Fjord

Answer 4

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.

Hagfish