CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4rails-activerecord
karianneberg
karianneberg

Reputation: 328

How do I combine uniq with select("distinct") without getting an invalid query?

I'm trying to combine a uniq statement with a select("distinct") statement in Active Record, and it results in two DISTINCT keywords, which of course leads to an invalid query. This is the simplest example I have come up with. (Mark that is is simplified in order to help you understand the problem - I'm not simply asking for how I get out distinct ids from a database.)

Product.all.uniq.select("distinct id").map(&:id)

This gives me this error message:
 Product Load (0.7ms)  SELECT DISTINCT distinct id FROM "products"
ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "distinct"
LINE 1: SELECT DISTINCT distinct id FROM "products"
                    ^
: SELECT DISTINCT distinct id FROM "products"

Why do I get two DISTRINCT keywords here? Is there any way to avoid it? Using uniq twice works, but I need to do a select for one of the filters I'm implementing.

Edit: The select("distinct..") has to go before the uniq statement.

Upvotes: 0

Views: 1550

Answers (2)

Matouš Borák
Matouš Borák

Reputation: 15934

You can use uniq(false) to disable a previously used uniq scope. So your example would go like this:

scope = Product.all.uniq
scope.uniq(false).select("distinct id").map(&:id)

Source code documentation can be found here.

Upvotes: 0

Simone Carletti
Simone Carletti

Reputation: 176352

uniq already uses DISTINCT. Either use uniq or select("DISTINCT"). Moreover, you should use pluck, and not map over the records and select the id.

What you really want to use is

Product.pluck(:id)

or

Product.all.ids

What's not clear to me, is why you want to use distinct. How comes an ID has duplicate values?

If the field is different than an id, simply use

Product.select("DISTINCT(field)").map(&:field)

or even better

Product.uniq.pluck(:field)

Hence in your case

Product.uniq.pluck(:id)
# => SELECT DISTINCT "products"."id" FROM "products"

Upvotes: 1

Related Questions