CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecordruby-on-rails-4
randomor
randomor

Reputation: 5663

Rails ActiveRecord Model.uniq.pluck :id doesn't work with postgres

I have an empty demo rails 4 app trying to do the following: Collection.order('created_at ASC').uniq.pluck :name It works under sqlite, but blows up in postgres with the following error: 

(0.9ms)  SELECT DISTINCT "collections"."name" FROM "collections" ORDER BY created_at ASC
PG::Error: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ...collections"."name" FROM "collections"   ORDER BY created_at...

Is this a bug or how can I fix it?

Upvotes: 4

Views: 2533

Answers (3)

randomor
randomor

Reputation: 5663

I was actually trying to solve this issue on active_admin. https://github.com/gregbell/active_admin/issues/2324

Now the solution seems to be Collection.reorder('name asc').uniq.pluck :name, this will overwrite the default_scope or previous order, and order collections with name. What's weird here is reorder works, while as order causes the problem...

Upvotes: 2

varatis
varatis

Reputation: 14740

This seems to be a problem with PostgreSQL. (Rails 3 DISTINCT QUERY)

To solve it, you could use select instead:

Collection.select([:name, :created_at]).order('created_at ASC').uniq.select(:name)

Or you could have Ruby get the names uniquely rather than SQL:

Collection.order('created_at ASC').pluck(:name).uniq

Upvotes: 2

cthulhu
cthulhu

Reputation: 3726

Collection.select([:id, :created_at]).order('created_at ASC').uniq.pluck(:id)

Upvotes: 1

Related Questions