ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list using order by with uniq

Question

I have this scope on my product model:

scope :all_products, lambda {
        joins(:prices)
        .where('prices.start_date <= ? and products.available = ?', Time.current, true)
        .uniq
        .order('CASE WHEN products.quantity >= products.min_quantity and (prices.finish_date IS NULL OR prices.finish_date >= now()) THEN 0 ELSE 1 END, prices.finish_date asc')
    }

I get the follow error when I try to run it: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

How I can use my order by and the query be uniq? I use rails 4.

Thang · Accepted Answer · 2019-12-24T15:53:32.003

1

You need to select the columns first so you can order them later in .order
The result will still have duplicated records in spite of using .uniq or .distinct because the generated query is SELECT DISTINCT products.*, prices.finish_date, ... tries to find all the combination of products.*, prices.finish_date and the special column that has a unique value (in this case you only want the products.id to be unique)

DISTINCT ON is the solution but using it is a little bit tricky for postgres because of SELECT DISTINCT ON expressions must match initial ORDER BY expressions.

Please try:

sub_query = Product.joins(:prices)
  .select("DISTINCT ON (products.id) products.*, CASE WHEN (products.quantity >= products.min_quantity) AND (prices.finish_date IS NULL OR prices.finish_date >= now()) THEN 0 ELSE 1 END AS t, prices.finish_date AS date")

query = Product.from("(#{sub_query.to_sql}) as tmp").select("tmp.*").order("tmp.t, tmp.date ASC")

edited Dec 24 '19 at 15:53

answered Dec 24 '19 at 05:06

Thang

811
1
5
12

It works...but I still have duplicates of products on my query result. The uniq does not work. – denispolicarpocampos Dec 24 '19 at 13:28
@denispolicarpocampos, sorry it should has been `.distinct` instead of `.uniq`. Updated answer – Thang Dec 24 '19 at 14:41
With .distinct I have the same duplicate...that is very weird... – denispolicarpocampos Dec 24 '19 at 14:55
@denispolicarpocampos, the issue you are facing is almost the same with https://stackoverflow.com/questions/32775220/rails-distinct-on-after-a-join. Both the `.distinct` and `.uniq` will still outputs duplicated records because the generated query tries to find all tries to find all the combination of `products.*, prices.finish_date and the special column` that has a unique value but we only want the `products.id` to be unique. I have updated my answer according to that post. – Thang Dec 24 '19 at 15:50

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list using order by with uniq

1 Answers1