0

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.

karianneberg
  • 329
  • 4
  • 17
  • I believe this is what you're looking for: http://stackoverflow.com/questions/9658881/rails-select-unique-values-from-a-column – Lucas Moulin Mar 06 '16 at 13:57
  • No, I1m not. That question asks why models with one field are not equal. I want to know why I get an invalid query from combining plain `uniq` with `select("distinct...")`, and how I can fix it. – karianneberg Mar 06 '16 at 14:03

2 Answers2

1

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"
Simone Carletti
  • 173,507
  • 49
  • 363
  • 364
  • This is a very simplified example in order to make it easier to understand. My real queries are much more advanced, and they are being built up over several lines of code and using scopes from other models. I know that `uniq` adds the `DISTINCT` sql keyword, but I have no way of knowing if one of the scopes added have already used the `uniq` statement or not. – karianneberg Mar 06 '16 at 14:06
  • Then do not oversimplify your example, otherwise we won't be able to provide an accurate answer. – Simone Carletti Mar 06 '16 at 14:14
0

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.

Matouš Borák
  • 15,606
  • 1
  • 42
  • 53
  • This works if the `uniq` is added before the `select("distinct...")`, but not after. Unfortunately, that's what I have to do. I'll update the question. – karianneberg Mar 06 '16 at 14:21
  • Well, perhaps you could use `unscope` in that case, something like `Product....select("distinct id").unscope(:select).uniq...`? – Matouš Borák Mar 06 '16 at 14:37
  • But I want the `select("distinct...")` to be used if it is defined. Am i forces to use a `.to_sql` check? – karianneberg Mar 06 '16 at 14:52
  • So the actual problem is that you want to use `uniq` if `select(distinct...)` has not been defined earlier and vice versa? In that case I'd go the to_sql check, although such usage seems a bit weird.... And by the way, if you used MySQL instead of Postgres, the above query with double `distinct`s would run fine as I've just found out. – Matouš Borák Mar 06 '16 at 15:08
  • Unfortunately, I cannot choose what database my customer uses. I guess I'll have to go with the `to_sql` check, then. – karianneberg Mar 06 '16 at 15:57