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?

Are you sure query with `uniq` returns different result? Please show actual SQL queries for each variant (use `sql` method). — taro, Mar 18 '12 at 14:01
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. — user984621, Mar 18 '12 at 14:47

score 31 · Accepted Answer · answered Jul 13 '12 at 19:00

31

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

answered Jul 13 '12 at 19:00

nslocum

5,037
1
27
27

2

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). – JohnMerlino Apr 03 '13 at 16:57
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? – Elliott Aug 06 '16 at 16:05

score 2 · Answer 2 · answered Jan 04 '13 at 16:27

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!

score 1 · Answer 3 · answered Mar 18 '12 at 16:18

1

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.

answered Mar 18 '12 at 16:18

Tom Harrison

13,533
3
49
77

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. – user984621 Mar 19 '12 at 08:33

score 0 · Answer 4 · edited Aug 06 '18 at 14:55

0

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.

edited Aug 06 '18 at 14:55

Sebastián Palma

32,692
6
40
59

answered Oct 04 '12 at 15:22

vanboom

1,274
12
20

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

4 Answers4

Linked